Find The 3rd Or Nth Highest Salary In A Table Via SubQuery

Rahul Bansal

9y546.1k20100Article

SQL Server SQL Query3rd Highest Salary in SQLFind 3rd Highest SalarySQL Nth Highest Salary

20 Comments

Join the conversation! Your thoughts help the community grow.

Subash10y ago
Useful one
Subash10y ago
Many interviews this questions asked
Darshan Thakkar10y ago
CREATE TABLE #Temp_Salary ( SALARY Decimal(10,2), EMPNAME VARCHAR(50)) INSERT INTO #Temp_Salary(SALARY,EMPNAME) VALUES(10000,'ABC') INSERT INTO #Temp_Salary(SALARY,EMPNAME) VALUES(12000,'ABC2') INSERT INTO #Temp_Salary(SALARY,EMPNAME) VALUES(10300,'AB3') INSERT INTO #Temp_Salary(SALARY,EMPNAME) VALUES(1400,'ABC4') INSERT INTO #Temp_Salary(SALARY,EMPNAME) VALUES(15000,'ABC5') INSERT INTO #Temp_Salary(SALARY,EMPNAME) VALUES(16000,'ABC6') INSERT INTO #Temp_Salary(SALARY,EMPNAME) VALUES(10050,'AB7') INSERT INTO #Temp_Salary(SALARY,EMPNAME) VALUES(10300,'ABC8') INSERT INTO #Temp_Salary(SALARY,EMPNAME) VALUES(17000,'ABC9') INSERT INTO #Temp_Salary(SALARY,EMPNAME) VALUES(10400,'ABC10') SELECT * FROM #Temp_Salary ORDER BY SALARY DESC SELECT SALARY,EMPNAME FROM #Temp_Salary ORDER BY SALARY DESC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY DROP TABLE #Temp_Salary
Hari Shanker10y ago
nice
Navratna Pawale10y ago
great..
Amit Kumar Singh10y ago
Nice Article
bhavesh sorathiya10y ago
Thanks.
Ankur Mistry10y ago
nice
Krunal Patel11y ago
Select TOP 1 esal as '3rd Highest Salary' from (SELECT DISTINCT TOP 3 esal from emp2 ORDER BY esal DESC) a ORDER BY esal ASC select * from emp2
Pankaj Hindlekar11y ago
I got answer : select MAX(salary)from emp11 where salary not in (select MAX(salary)from emp11);
Harieswaran D11y ago
SELECT a.PLAN_NOFROM ( SELECT D.PLAN_NO,ROW_NUMBER() OVER(ORDER BY D.PLAN_NO DESC) As RowNum FROM PL_ASP_PLN_HDR D ) As A WHERE A.RowNum =2
Pankaj Hindlekar11y ago
realy it was helpful to me..but i have 1 quetion : how to retrive only 2nd highest salary without 1st
Manish Kumar Choudhary11y ago
Nice one Rahul Bansal sir..
Muthu Vijayan S11y ago
Hi good.http://www.dotnetpgm.com/2014/11/sql-query-to-find-nth-highest-or-lowest.html
Rahul Bansal12y ago
@vithal:- i have used 'Distinct' in inner query to avoid the duplicate salary..:)
Vithal Wadje12y ago
its fails when we have duplicate salary in column
virender singh12y ago
good rahul but how we can find the second, third, fourth, five & nth salary through 1 sql query???
Arun Gupta12y ago
really good explanation rahul.
Rahul Bansal12y ago
Thanks Nagraj
Nagaraj S12y ago
@Rahul Bansai Very good explanation. Expecting more articles from you in the near future.

To find the Nth highest salary, we need to create a table in the database containing some data and to do this use the following procedure.

Step 1

Create a schema of a table named "Employee" in your database as in the following,

create table Employee
(
ID int identity,
Name varchar(20),
Salary float,
Department varchar(20)
)

Create a table

Note

I am using SQL Server 2008 in this demo.

Step 2

Insert some values in the table according to the column behaviour like Name and Data Type.

Insert into Employee(Name,Salary,Department)values('A',20000,'Finance')
Insert into Employee(Name,Salary,Department)values('B',10000,'Finance')
Insert into Employee(Name,Salary,Department)values('C',28000,'IT')
Insert into Employee(Name,Salary,Department)values('D',15000,'Finance')
Insert into Employee(Name,Salary,Department)values('E',39000,'Finance')
Insert into Employee(Name,Salary,Department)values('F',12000,'Finance')
Insert into Employee(Name,Salary,Department)values('G',40000,'IT')
Insert into Employee(Name,Salary,Department)values('H',32000,'Finance')
Insert into Employee(Name,Salary,Department)values('I',56000,'IT')
Insert into Employee(Name,Salary,Department)values('J',29000,'Finance')

Step 3

Use the following command to see the data from the table.

select * from Employee

Question How can I get the Nth highest salary like 3rd highest or 4th highest?

Answer

It can be done in many ways but I will demonstrate the easiest way that is very famous and compact. Before getting the Nth Highest salary, we will get the salarys of employees in decreasing order.

SELECT Name,salary FROM employee ORDER BY salary desc

Note

I am assuming that we want to find the 3rd highest salary.

Explanation

In the preceding query my motive is that first I will get the highest 3 salaries and then get the minimum salary from those 3 salaries.

There are the following 2 parts of the preceding query,

Inner Query - Get the highest 3 salaries
Outer Query - Get the minimum salary from those 3 salaries

In the Inner Query I have used the "DISTINCT", "TOP", "ORDER BY" and "DESC" keywords, they mean,

DISTINCT - for removing the duplicity.
TOP - to get the number of upper rows from the set of records like here 3 is used as number.
ORDER BY - to ordering the set of rows.
DESC - used with "ORDER BY" to get the data in decreasing order.

So by the inner query we will get output like this,

Now to find the minimum salary from the inner query. To do that I will write the outer query using the "MIN" keyword and aliase the set by "as a" to get the final output like this,

SELECT MIN( salary)
FROM (
-- INNER Query
) as a

MIN to get the minimum record from the set.

as a for the aliasing of the set of records like here the result of the inner query is aliasing "as a".

Final Output

The output will be 39000.