Why wouldn't you just use the RANK or ROW_NUMBER functions within in CTE?

I think it'll be more easier if the ROW_NUMBER() is used.

If I used row_number(), i can only exclude the first line based on this, but what if two employees have the same top salary ?

With salary in the range of 15000, 15000, 10000, 5000 : i will return 15000 although the second is 10000, right ?

I think you can get the Depart and Salary in the CTE, then you join the CTE with CTE itself by Department and Salary.

Here is my example, I think it works. ;WITH T AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Ord FROM @Employees ) SELECT * FROM T t1 WHERE EXISTS ( SELECT * FROM T t2 WHERE t1.Department = t2.Department AND t2.Ord = 2 AND t1.Salary=t2.Salary

)

@cverde: you are right. Using ROW_NUMBER() will not account for ties. RANK() will.

It's get a lot trickier when considering tie ! =D But already pretty instructive for a beginner puzzle.

@lyqandgdp : Thanks for your solution, i will try to use more CTE enven if i'm not so comfortable with them.

@Marc Jellinek : Indeed Rank() is worthly but I'm still struggling because when there is a tie, it numbers them : '1,1,3' and then '1,1,1,4', ... so no number 2 to look for. But I thinks it's the best way. Thanks for your help.

Finally found the solution, we have to use DENSE_RANK() to avoid the gap in rank.

I will post another answer with it.

you would be in difficulty if user change requirement to ask 10th highest salary for department....