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.

xp_readerrorlog/sp_readerrorlog in SQL Server

xp_readerrorlog/sp_readerrorlog

SQL Server support undocumented extended stored procedures (xp_readerrorlog) to read error logs, we can also see proc sp_readerrorlog, which ultimately uses procedure xp_readerrorlog in its definition

t-SQL code of sp_readerrorlog:



create proc sys.sp_readerrorlog(
@p1 int = 0,
@p2 int = NULL,
@p3 nvarchar(4000) = NULL,
@p4 nvarchar(4000) = NULL)
as
begin

if (not is_srvrolemember(N'securityadmin') = 1)
begin
raiserror(15003,-1,-1, N'securityadmin')
return (1)
end

if (@p2 is NULL)
exec sys.xp_readerrorlog @p1
else
exec sys.xp_readerrorlog @p1,@p2,@p3,@p4
end




When we execute xp_readerrorlog/sp_readerrorlog it returns sample result as below.




As per definition of sp_readerrorlog, procedure will accept four parameters (@p1, @p2, @p3, and @p4)
Where parameters requires values as below.
@p1 [Log Number] : 0 for current log, 1 for log achieve#1, 2 for log achieve#2 etc…
@p2 [Log Type] : 1  Read SQL server error log, 2  Read SQL server agent error log.
@p3 [Search String1]: first parameter value to search in field Text
@p4 [Search String4]: second parameter value to search in field Text

All above parameters also have default values as 0 or NULL.

Let us take below example for sp_readerrorlog
EXEC sys.sp_readerrorlog 0, 1 ,'failed','user'
According to above statement procedure will read errors from current SQL Server error Log.

In real, actual extended stored procedure is XP_readerrorlog, it accept three more parameters
StartTime,EndTime,SortOrder these three parameters satisfy column LogDate from procedure result

Example:
EXEC sys.xp_readerrorlog 0, 1 , N'failed',N'user','2015-05-25 15:13:02.410','2015-06-15 13:54:10.550','ASC';

According to above statement procedure will read errors from current SQL Server error Log and will have filter criteria on LogDate column as startDate and EndDate in Ascending order

Procedure with required sample parameters name are:

EXEC XP_readerrorlog [LogNumber],[LogType],[SearchStr1],[SearchStr2],[StartTime],[EndTime],[SortOrder]