sql - How to select top N salaries for each person? -

i have 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 select top 2 salaries each people.

i try understand cross apply. looks invented task conform cross apply.

now have following query

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

look enough far expected result.

expected result:

1    180 1    200 2    40 2    45 

you can use outer apply top 2:

select distinct              y.[user_id],             d.salary,             d.[month] yourtable y outer apply(     select top 2  *     yourtable     y.[user_id] = [user_id]     order [user_id], salary desc     ) d order [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 ( select  *,         row_number() on (partition [user_id] order salary desc) rn yourtable )  select [user_id], salary, [month] cte  rn <= 2 

same output.