T-SQL Script to find out Nth highest salary

Updated: Jan 7

It is a standard SQL interview question where the interviewer asks to write a query that finds Nth highest salary from the employee table. How do you write that SQL query?


Let's create a sample table and insert records into it.


Create table Employee (id int, name varchar(10),salary int);
Go
Insert into Employee values(1,'A',100);
Insert into Employee values(2,'B',200);
Insert into Employee values(3,'C',300);
Insert into Employee values(4,'D',500);
Insert into Employee values(5,'E',500);
Insert into Employee values(6,'F',900);
Go

SQL Script to find out the highest salary

SELECT A.ID,A.NAME,A.SALARY 
FROM EMPLOYEE A
WHERE 0 = (SELECT COUNT( DISTINCT SALARY) FROM EMPLOYEE B WHERE B.SALARY > A.SALARY);

SQL Script to find out the highest salary
SQL Script to find out the 2nd highest salary

The below script returns the 2nd highest salary in SQL Server.


SELECT A.ID,A.NAME,A.SALARY 
FROM EMPLOYEE A
WHERE 1 = (SELECT COUNT( DISTINCT SALARY) 
FROM EMPLOYEE B WHERE B.SALARY > A.SALARY);

SQL Script to find out the 2nd highest salary

You can use the following generic SQL script to find out Nth salary in the Employee table.


In the script, replace N with the position. For example, for the top (highest) employee salary use N=1

SELECT A.ID,A.NAME,A.SALARY 
FROM EMPLOYEE A
WHERE N-1 = (SELECT COUNT( DISTINCT SALARY) 
FROM EMPLOYEE B WHERE B.SALARY > A.SALARY); 

26 views0 comments