sqlsql-serverhighest

3rd highest salary sql


I try to get all 3 highest salary from top 5 employees like this

    salary
  35000
  34000
  20000
  12000
  500
  40000
  25000
  41000
  90000
  550000

query

select top 5
  (SELECT MAX(grosssalary) FROM Detail) maxsalary ,
   (SELECT MAX(grosssalary) FROM Detail) sec_max_salary,
  (SELECT MAX(grosssalary) FROM Detail
  WHERE grosssalary NOT IN (SELECT MAX(grosssalary) FROM Detail )) as third_max_salary

but this shows data like this

maxsalary   sec_max_salary  third_max_salary
550000       550000            41000

where as i want data like this

   maxsalary    sec_max_salary  third_max_salary
    550000       90000            41000

Solution

  • Do a CTE and get the ROWNUMBER() on salary DESC and in outer query fetch the record with rownumber equal to 3.

    ;WITH CTE AS
    (
        SELECT RN = ROW_NUMBER() OVER (ORDER BY salary DESC),
               Salary
         FROM [YourTable]
     )
    
     SELECT Salary
     FROM CTE
     WHERE RN <= 3
    

    Note: If you want 3rd highest salary use RN=3 if you want all top 3 salary then use RN<=3

    If you want top 3 highest salary then you can do this as well:

    SELECT TOP 3 Salary
    FROM [YourTable]
    ORDER BY Salary DESC