Tuesday, June 16, 2015

Nth Highest Salary in SQL Server

Please find Below Script to get 14 different ways to find Nth Highest salary.

In script, I have selected 3rd highest salary.
For any other highest salary, Please modify queries accordingly
i.e in below Queries for 5th Highest 3 need to replace by 5 except Nested Sub Queries.

There might be some other ways too, as of now I was able to figure out below.


CREATE TABLE Employees
(
Empid INT IDENTITY(1, 1),
NAME VARCHAR(20),
Salary INT,
DeptId INT
)

INSERT INTO Employees
(NAME,
Salary,
DeptId)
VALUES ('Dinesh', 5000, 1),
('Babu' , 4000, 1),
('Verma' , 6000, 2),
('Allen' , 7000, 3),
('Joseph', 8000, 1),
('Denny' , 1000, 5),
('Ramy' , 2000, 5),
('Ramesh', 2000, 1),
('John' , 9000, 2),
('Mohan' , 5000, 1),
('Sohan' , 5000, 1),
('Brant' , 8000, 1),
('Hogg' , 6000, 3)


-- First Check 3rd Highest Salary using query with Order by Clause
SELECT *
FROM Employees
ORDER BY Salary DESC
-- According to Above query Output, 3rd highest salary is 7000

--1. Using Analytical Dense_Rank Function
SELECT TOP 1 Salary
FROM (SELECT TOP 100 PERCENT NAME,
Salary,
Dense_rank()
OVER(
ORDER BY Salary DESC) AS SalRank
FROM Employees
ORDER BY Salary DESC) EmpSalary
WHERE salrank = 3

--2. Using Nested Sub Queries
SELECT Max(Salary) AS Salary
FROM Employees
WHERE Salary < (SELECT Max(Salary) FROM Employees WHERE Salary < (SELECT Max(Salary) FROM Employees)) /*/>/>*/

--3. Using Corelated Sub Queries
SELECT TOP 1 Salary
FROM Employees e
WHERE 3 = (SELECT Count(DISTINCT Salary)
FROM Employees ee
WHERE e.Salary <= ee.Salary) /*/>/>*/

--4. Using Min and Top Clause
SELECT Min(Salary) AS Salary
FROM (SELECT DISTINCT TOP 3 Salary
FROM Employees e
ORDER BY Salary DESC) Sal

--5. Using Min,Top and Order by Clause
SELECT TOP 1 Salary
FROM (SELECT DISTINCT TOP 3 Salary
FROM Employees e
ORDER BY Salary DESC) Sal
ORDER BY Salary ASC

--6. Using MAX, Top, NOT IN and Order BY Clause
SELECT Max(Salary) AS Salary
FROM Employees
WHERE Salary NOT IN (SELECT DISTINCT TOP (3-1) Salary
FROM Employees e
ORDER BY Salary DESC)

--7. Using Top, NOT IN and Order BY Clause
SELECT TOP 1 Salary
FROM Employees
WHERE Salary NOT IN (SELECT DISTINCT TOP (3-1) Salary
FROM Employees e
ORDER BY Salary DESC)
ORDER BY Salary DESC

--8. Using MIN, INNER JOIN,Top and Order BY Clause
SELECT Min(e.Salary) AS Salary
FROM Employees e
INNER JOIN (SELECT DISTINCT TOP 3 Salary
FROM Employees e
ORDER BY Salary DESC) Sal
ON e.Salary = Sal.Salary

--9. Using MIN, INNER JOIN,Top and Order BY Clause
SELECT TOP 1 e.Salary
FROM Employees e
INNER JOIN (SELECT DISTINCT TOP 3 Salary
FROM Employees e
ORDER BY Salary DESC) Sal
ON e.Salary = Sal.Salary
ORDER BY e.Salary ASC

--10. Using LEFT JOIN,MAX, Top and Order BY Clause
SELECT Max(e.Salary) AS Salary
FROM Employees e
LEFT JOIN (SELECT DISTINCT TOP (3-1) Salary
FROM Employees e
ORDER BY Salary DESC) Sal
ON e.Salary = Sal.Salary
WHERE Sal.Salary IS NULL

--11. Using LEFT JOIN,Top and Order BY Clause
SELECT TOP 1 e.Salary
FROM Employees e
LEFT JOIN (SELECT DISTINCT TOP (3-1) Salary
FROM Employees e
ORDER BY Salary DESC) Sal
ON e.Salary = Sal.Salary
WHERE Sal.Salary IS NULL
ORDER BY e.Salary DESC

--12. Using RIGHT JOIN,Top and Order BY Clause
SELECT TOP 1 Sal.Salary
FROM Employees e
RIGHT JOIN (SELECT DISTINCT TOP 3 Salary
FROM Employees e
ORDER BY Salary DESC) Sal
ON e.Salary = Sal.Salary
ORDER BY e.Salary ASC

--13. Using MIN, RIGHT JOIN,Top and Order BY Clause
SELECT Min(Sal.Salary) AS Salary
FROM Employees e
RIGHT JOIN (SELECT DISTINCT TOP 3 Salary
FROM Employees e
ORDER BY Salary DESC) Sal
ON e.Salary = Sal.Salary


--14. Using OffSET, FETCH and Order BY Clause
SELECT DISTINCT Salary FROM Employees
ORDER BY salary DESC
OFFSET 3-1 ROWS
FETCH NEXT 1 ROWS ONLY


Please make sure you are trying this in development environment.


No comments :

Post a Comment