3rd highest salary sql

Question

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

Answer 1

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