vote buttons
11
1
intermediate
0
beginner
0
advanced
Write the Query assuming an Employee table with Salary column.
17-Jun-2015 05:48 UTC
kube
16

2 Answers

vote buttons
10
Query to find 5th Highest salary without using SubQuery / Top. (Change 5 to any N value to get Nth highest Salary)
WITH Salaries AS
(
  SELECT 
    Salary, ROW_NUMBER() OVER(ORDER BY Salary DESC) AS 'RowNum'
  FROM 
    Employee
)
SELECT
  Salary
FROM
  Salaries
WHERE
  RowNum = 5
17-Jun-2015 06:11 UTC
K Bonneau
336
vote buttons
5

Using Correlated SubQuery, N = 10:

SELECT 
  Salary
FROM 
  Employee Emp1
WHERE 9 = /* 10th Highest Salary, N - 1 */
(
  SELECT 
    COUNT(DISTINCT(Emp2.Salary))
  FROM 
    Employee Emp2
  WHERE 
    Emp2.Salary > Emp1.Salary
)



17-Jun-2015 05:49 UTC
kube
16