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

Try this

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

For 3 you can replace any value...

Answer 2

Too simple if you use the sub query!

SELECT MIN(EmpSalary) from (
SELECT EmpSalary from Employee ORDER BY EmpSalary DESC LIMIT 3
);

You can here just change the nth value after the LIMIT constraint.

Here in this the Sub query Select EmpSalary from Employee Order by EmpSalary DESC Limit 3; would return the top 3 salaries of the Employees. Out of the result we will choose the Minimum salary using MIN command to get the 3rd TOP salary of the employee.

Answer 3

To get third highest value from table

SELECT * FROM tableName ORDER BY columnName DESC LIMIT 2, 1

Answer 4

Third or nth maximum salary from salary table without using subquery

select salary from salary
   ORDER   BY salary DESC
   OFFSET  N-1 ROWS
   FETCH NEXT 1 ROWS ONLY

For 3rd highest salary put 2 in place of N-1

Answer 5

Optimized way: Instead of subquery just use limit.

select distinct salary from employee order by salary desc limit nth, 1;

See limit syntax here http://www.mysqltutorial.org/mysql-limit.aspx

Answer 6

Showing all 3rd highest salary:

select * from emp where sal=
(SELECT DISTINCT sal FROM emp ORDER BY sal DESC LIMIT 3,1) ;

Showing only 3rd highest salary:

SELECT DISTINCT sal FROM emp ORDER BY sal DESC LIMIT 3,1