Advertisement
Advertisement


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

Accepted Answer

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