## How to find third or nth maximum salary from salary table?

### Question

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

2015/09/11
1
97
9/11/2015 8:21:29 PM

Use `ROW_NUMBER`(if you want a single) or `DENSE_RANK`(for all related rows):

``````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
``````
2017/09/27
82
9/27/2017 7:32:22 AM

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...

2013/04/26

If you want optimize way means use `TOP` Keyword, So the nth max and min salaries query as follows but the queries look like a tricky as in reverse order by using aggregate function names:

N maximum salary:

``````SELECT MIN(EmpSalary)
FROM Salary
WHERE EmpSalary IN(SELECT TOP N EmpSalary FROM Salary ORDER BY EmpSalary DESC)
``````

for Ex: 3 maximum salary:

``````SELECT MIN(EmpSalary)
FROM Salary
WHERE EmpSalary IN(SELECT TOP 3 EmpSalary FROM Salary ORDER BY EmpSalary DESC)
``````

N minimum salary:

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

for Ex: 3 minimum salary:

``````SELECT MAX(EmpSalary)
FROM Salary
WHERE EmpSalary IN(SELECT TOP 3 EmpSalary FROM Salary ORDER BY EmpSalary ASC)
``````
2018/11/05

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.

2014/08/22

Replace N with your Max Number

``````SELECT *
FROM Employee Emp1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)
``````

Explanation

The query above can be quite confusing if you have not seen anything like it before – the inner query is what’s called a correlated sub-query because the inner query (the subquery) uses a value from the outer query (in this case the Emp1 table) in it’s WHERE clause.

And Source

2013/04/26

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

2016/06/11