How to choose the two highest prices
This is the input.
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 come back
Sort:Insurance Carrier 100 - 22.00 and 21.00
Not 22.00 and 22.00 I am afraid this code will return 22 and 22, possibly the 2 best prices, but it really is not.
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]
source to share
If you want the same value to have the same number, you should use dense_rank()
instead row_number()
. But you are on the right track!
Also change sum()
to max()
to not add the values to the same dense_rank()
.
Try the following:
select
[dept]
, [Sort: Procedure Code] as Code
, [Sort: Insurance Carrier]
, max(case when num = 1 then [Pmt Amount] else 0 end) as [first high]
, max(case when num = 2 then [Pmt Amount] else 0 end) as [second high]
from (
select
dense_rank() 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]
Demo version of rexter: http://rextester.com/PJCDDC90476
returns:
+-----------+------+-------------------------+------------+-------------+
| dept | Code | Sort: Insurance Carrier | first high | second high |
+-----------+------+-------------------------+------------+-------------+
| Gardeners | 100 | Sort:Insurance Carrier | 22.00 | 21.00 |
+-----------+------+-------------------------+------------+-------------+
source to share
You seem to want dense_rank()
instead row_number()
:
SELECT [DEPT], [Sort: Procedure Code] as Code, [Sort: Insurance Carrier],
SUM(CASE WHEN num = 1 THEN [Pmt Amount] END) AS [first high],
SUM(CASE WHEN num = 2 THEN [Pmt Amount] END) AS [second high]
FROM (SELECT DENSE_RANK() OVER (PARTITION BY [DEPT], [Sort: Procedure Code], [Sort: Insurance Carrier]
ORDER BY [Pmt Amount] DESC
) AS num,
rd.*
FROM [revenuedetail$] rd
) rd
WHERE num IN (1, 2)
GROUP BY [DEPT], [Sort: Procedure Code], [Sort: Insurance Carrier];
Notes:
- I deleted
ELSE 0
. If there is no second value, then this version returnsNULL
, not0
. I find this more intuitive (addELSE 0
back if this is not the behavior you want). - I added more meaningful table aliases.
rd
makes more sense thant
. - I used
rd.*
in a subquery. This effectively shortens the query and makes it easier to modify. - You should reconsider the column names. All the square brackets just make the code harder to write and read.
source to share
If sql server version is 2012 and above then Lead () can be used:
select Top 1 [DEPT], [Sort: Procedure Code], [Sort: Insurance Carrier],
[Pmt Amount] AS [first high],
Lead([Pmt Amount],1)over(partition by [DEPT], [Sort: Procedure Code],
[Sort: Insurance Carrier] ORDER BY [Pmt Amount] DESC)AS [Second high]
from [revenuedetail$] order by [Pmt Amount] desc
source to share