gstackoverflow gstackoverflow - 2 months ago 9
SQL Question

How to select top N salaries for each person?

I have the table

user_id salary month
1 100 1
1 150 2
1 200 3
1 180 4
1 140 5
2 10 1
2 40 2
2 20 3
2 15 4
2 45 5


I want to select top 2 salaries for each people.

I try to understand cross apply. looks like my invented task conform the cross apply.

Now I have the following query

select distinct(s.user_id) from Salary s
cross apply (
select top 2 * from Salary sal
order by sal.salary desc
)sal


look like I enough far from expected result.

expected result:

1 180
1 200
2 40
2 45

Answer

You can use OUTER APPLY with TOP 2:

SELECT DISTINCT 
            y.[user_id],
            d.salary,
            d.[month]
FROM YourTable y
OUTER APPLY(
    SELECT TOP 2  *
    FROM YourTable
    WHERE y.[user_id] = [user_id]
    ORDER BY [user_id], salary DESC
    ) as d
ORDER BY [user_id], salary DESC

Will return:

user_id salary  month
1       200     3
1       180     4
2       45      5
2       40      2

Another way:

;WITH cte AS (
SELECT  *,
        ROW_NUMBER() OVER (PARTITION BY [user_id] ORDER BY salary DESC) as rn
FROM YourTable
)

SELECT [user_id], salary, [month]
FROM cte 
WHERE rn <= 2

Same output.

Comments