DENSE_RANK and CTE to handle tie result, i modified slightly the test data to provide more different cases....

Finally found the solution, we have to use DENSE_RANK() to avoid the gap in rank. I will post another answer with it....

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...

I think you need to order by DESC on line 39 to get the second highest as it returns curretnly the second lowest ....

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 ?...

The challenge is to find the employees with the second highest salary in each department. However, it is a little more complicated because if two employees have the same salary, you need to list both of them.

...

Simple solution with 2 inner join, not very elegant but works at least on SQL 2008....