How to find third or nth maximum salary from salar

Question

How to find third or nth maximum salary from salary table(EmpID,EmpName,EmpSalary) in Optimized way?

Answer 1

You can try this:

select top(1) EXPORT_NO
from DC_HDR 
order by CASE when  (ROW_NUMBER() over(order by EXPORT_NO desc))=3 then EXPORT_NO else 0 end desc

Answer 2

--nth highest salary

select * 
from (select lstName, salary, row_number() over( order by salary desc) as rn 
      from employee) tmp
where rn = 2

--(nth -1) highest salary

select * 
from employee e1
where 1 = (select count(distinct salary)  
           from employee e2
           where e2.Salary > e1.Salary )

Answer 3

SELECT EmpSalary 
FROM salary_table 
GROUP BY EmpSalary 
ORDER BY EmpSalary DESC LIMIT n-1, 1;

Answer 4

Method 1:

SELECT TOP 1 salary FROM (
SELECT TOP 3 salary 
 FROM employees 
  ORDER BY salary DESC) AS emp 
 ORDER BY salary ASC

Method 2:

  Select EmpName,salary from
  (
    select EmpName,salary ,Row_Number() over(order by salary desc) as rowid      
     from EmpTbl)
   as a where rowid=3

Answer 5

Select TOP 1 Salary as '3rd Highest Salary' from (SELECT DISTINCT TOP 3 Salary from Employee ORDER BY Salary DESC) a ORDER BY Salary ASC;

I am showing 3rd highest salary

Answer 6

SELECT MIN(COLUMN_NAME)
FROM   (
           SELECT DISTINCT TOP 3     COLUMN_NAME
           FROM   TABLE_NAME
           ORDER BY
                  COLUMN_NAME        DESC
       ) AS 'COLUMN_NAME'