HOW TO FIND Nth Highest Salary in SQL Server

HOW TO FIND Nth Highest Salary in SQL Server ?

This is one of the most common SQL Server interview questions that how to find the Nth highest salary of employee with their details?.

Where N could be any number for e.g. 1, 2,3, ..and so on

Lets create a employee table and insert some dummy records into a table.

CREATE TABLE Employee (EmpName varchar(10), salary int);

INSERT INTO Employee VALUES ('Molly', 1000); 
INSERT INTO Employee VALUES ('Rozer', 2000);
INSERT INTO Employee VALUES ('Jhonny', 3000); 
INSERT INTO Employee VALUES ('Williams', 4000); 
INSERT INTO Employee VALUES ('Ronaldo', 5000);

Nth Highest salary using Row_Number 

Following statement uses Row_Number() function to get the 3rd highest salary.

SELECT EmpName , Salary FROM(

SELECT ROW_NUMBER() OVER(ORDER BY Salary DESC) AS SNo , EmpName, Salary

FROM Employee

)Sal

WHERE SNo = 3

As you can see, In employee table 3rd highest salary is 3000 and query returns the same value.

Nth Highest salary Using TOP Keyword

Following statement uses TOP keyword to get the 2nd highest salary.

SELECT TOP 1 * FROM (

SELECT TOP 2 EmpName , Salary FROM Employee ORDER BY Salary DESC

) Sal ORDER BY Salary ;

 

As you can see, In employee table 2nd highest salary is 4000 and query returns the same value.

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 13,387 total views,  2 views today