T-SQL Script to find out Nth highest salary

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 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);

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);