Basic Logics · Data Analysis · Real Scenario Logic's

Simple Query to find Nth Highest and Lowest Salary from the list of Non Unique Salary Records in SQL Server

Introduction

In this Article, we will see how to find the Nth Highest and Lowest Salary from the list of Non Unique Salary records by using Simple SQL Query in SQL Server.

Demo Table – SQL Script

IF OBJECT_ID('Tempdb..#Employee') IS NOT NULL
	DROP TABLE #Employee;

CREATE TABLE #Employee
(EmployeeName VARCHAR(75), Salary MONEY); 

INSERT INTO #Employee 
VALUES 
 ('A', 10000)
,('B', 20000)
,('C', 30000)
,('D', 30000)
,('E', 20000)
,('F', 40000)
,('G', 5000); 

SELECT * FROM #Employee;

Demo Table – Table with Data

Finding Nth Highest Salary

There are many possible ways available to do the same. Hereby, doing the simple way using DISTINCT, DENSE_RANK() and SUB QUERY for finding Nth Highest Salary. We Can also use Common Table Expression(CTE) or Table Variable or Temporary Table instead of Sub Query.

SQL SCRIPT

--Nth Highest Salary
DECLARE @Nth_Highest_Salary INT;
SET @Nth_Highest_Salary=3;

SELECT Salary AS Nth_Highest_Salary FROM 
(
	SELECT DISTINCT Salary,DENSE_RANK() OVER(ORDER BY Salary DESC) AS R_NO FROM #Employee
) A
WHERE R_NO=@Nth_Highest_Salary;

Image Pic

Finding Nth Lowest Salary

To find the Nth Lowest Salary, a simple change in sorting order(ASC instead of DESC) is enough in the same above query of finding the Nth Highest salary. Also Note that Default Sorting Order is ASC and it is Optional to mention.

SQL SCRIPT

--Nth Lowest Salary
DECLARE @Nth_Lowest_Salary INT;
SET @Nth_Lowest_Salary=2;

SELECT Salary AS Nth_Lowest_Salary FROM 
(
	SELECT DISTINCT Salary,DENSE_RANK() OVER(ORDER BY Salary ASC) AS R_NO 
	FROM #Employee
) A
WHERE R_NO=@Nth_Lowest_Salary;

IMAGE PIC

Conclusion

In this Article, we saw how to use simple query and finding the Nth Highest and Lowest Salary from the list of Non Unique Salary records in SQL Server. These Queries are tested with both On-Premises and Azure Sql db.

Thanks for reading and Follow Blog and Show your Support for many more interesting upcoming Posts!

Advertisement

2 thoughts on “Simple Query to find Nth Highest and Lowest Salary from the list of Non Unique Salary Records in SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s