How to find third or nth maximum salary from salary table?
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?
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
Popular Answer
Row Number :
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)
Sub Query :
SELECT *
FROM Employee Emp1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary
)
Top Keyword :
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC
) a
ORDER BY salary
Read more… Read less…
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...
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)
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.
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
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