How to find nth max salary from Employee.

 Mostly    it    required   to   execute    this    query   while  developing   java applications

 and   this  most   frequent  question  asked in interview  for mostly experience  developers.

These   queries    are   given   mostly   used    for   search  by  developers and  DBA.

WITH CTE AS

(

    SELECT EmpID, EmpName, EmpSalary,

           RN = ROW_NUMBER() OVER (ORDER BY EmpSalary DESC)

    FROM dbo.Salary

)

SELECT EmpID, EmpName, EmpSalary

FROM CTE

WHERE RN = @NthRow

Oracle Database

---------------------

SELECT Salary,EmpName

FROM

  (

   SELECT Salary,EmpName,ROW_NUMBER() OVER(ORDER BY Salary) As RowNum

   FROM EMPLOYEE

   ) As A

WHERE A.RowNum IN (2,3)

SELECT *

FROM Employee Emp1

WHERE (N-1) = (

               SELECT COUNT(DISTINCT(Emp2.Salary))

               FROM Employee Emp2

               WHERE Emp2.Salary > Emp1.Salary

               )

 select distinct salary from Empoyee e1 where 2 = (select count(distinct salary) from Employee e2 where  e1.salary <= e2.salary);

MSSQL  Database

-----------------------------

SELECT TOP 1 salary

FROM (

      SELECT DISTINCT TOP n salary

      FROM employee

      ORDER BY salary DESC

      ) a

ORDER BY salary

SELECT MAX(EmpSalary)

FROM Salary

WHERE EmpSalary IN(SELECT TOP N EmpSalary FROM Salary ORDER BY EmpSalary ASC)

MySql  Database

-------------------------

SELECT EmpSalary

FROM salary_table

GROUP BY EmpSalary

ORDER BY EmpSalary DESC LIMIT n-1, 1;

Third highest salary

----------------------------

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

-->