How to pick the 2 highest prices paid

Viewed 81

This is the input data.

  Dept            Company                   Code      Payment Amt

Gardeners     Sort:Insurance Carrier        100         20.00
Gardeners     Sort:Insurance Carrier        100         22.00
Gardeners     Sort:Insurance Carrier        100         21.00
Gardeners     Sort:Insurance Carrier        100         20.00
Gardeners     Sort:Insurance Carrier        100         22.00 

I want to return

Sort:Insurance Carrier 100   -  22.00 and 21.00

Not 22.00 and 22.00 I fear this code is returning 22 and 22 arguably the 2 top prices paid but not really so.

I have this SQL

SELECT 
[DEPT], [Sort: Procedure Code] as Code, [Sort: Insurance Carrier], 
SUM(CASE WHEN num = 1 THEN [Pmt Amount] ELSE 0 END) AS [first high], 
SUM(CASE WHEN num = 2 THEN [Pmt Amount] ELSE 0 END) AS [second high] 
FROM 
(
SELECT ROW_NUMBER() OVER(PARTITION BY 
[DEPT], [Sort: Procedure Code], [Sort: Insurance Carrier]
ORDER BY [Pmt Amount] DESC) AS num, 
[DEPT], [Sort: Procedure Code], [Sort: Insurance Carrier], 
[Pmt Amount]
FROM 
[revenuedetail$]
) AS t 
WHERE num IN (1, 2)
GROUP BY [DEPT], [Sort: Procedure Code], [Sort: Insurance Carrier]
3 Answers
Related