[Challenge] 找出各部門第二高薪人員

Beyond Relational TSQL Beginner's Challenge 1，個人是利用次序函數 RANK() 來做到，次序函數是從 SQL Server 2005 才開始有，在眾多的解法中，有找到非次序函數的解法，可以參考看看。^_^

• 需求：找出各部門第二高薪人員，而且假如第二高薪有多個人員，也必須一併列出來。

• 資料來源（題目內有資料來源的 script）

EmployeeID  EmployeeName    Department      Salary   
----------- --------------- --------------- ---------
1           T Cook          Finance         40000.00
2           D Michael       Finance         25000.00
3           A Smith         Finance         25000.00
4           D Adams         Finance         15000.00
5           M Williams      IT              80000.00
6           D Jones         IT              40000.00
7           J Miller        IT              50000.00
8           L Lewis         IT              50000.00
9           A Anderson      Back-Office     25000.00
10          S Martin        Back-Office     15000.00
11          J Garcia        Back-Office     15000.00
12          T Clerk         Back-Office     10000.00

• 利用次序函數 RANK()

SELECT T.EmployeeID , T.EmployeeName, T.Department, T.Salary
FROM
  (
    SELECT EmployeeID , EmployeeName, Department, Salary ,
            RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RANKNO
     FROM @Employees
  ) AS T
WHERE RANKNO = 2

• 值得參考的解法

SELECT EmployeeID,EmployeeName,Department,Salary
FROM @Employees e0
WHERE
      (
        SELECT Count(DISTINCT e1.Salary) 
        FROM @Employees e1 
        WHERE e1.Salary >= e0.Salary 
                AND e1.Department = e0.Department
      ) = 2
ORDER BY Department, EmployeeID

• 結果

EmployeeID  EmployeeName    Department      Salary   
----------- --------------- --------------- ---------
10          S Martin        Back-Office     15000.00
11          J Garcia        Back-Office     15000.00
2           D Michael       Finance         25000.00
3           A Smith         Finance         25000.00
7           J Miller        IT              50000.00
8           L Lewis         IT              50000.00

參考資料： 

• TSQL Beginner’s Challenge #1 – Find the second highest salary for each department - 題目
• MSDN 次序函數