SQL Query to get second highest salary

In this short post I am touching a very frequently asked question in interviews to get the second highest salary from a table. :-)

Here is my take on this question:

Lets say we have a table EMPLOYEE.

CREATE TABLE EMPLOYEE

(

    EMPNAME nvarchar(30),

    SALARY DECIMAL(18,2)

)

Put some data in this table:

INSERT INTO EMPLOYEE VALUES('ALEX', 1000)

INSERT INTO EMPLOYEE VALUES('JIM', 1000)

INSERT INTO EMPLOYEE VALUES('BRIAN', 2000)

INSERT INTO EMPLOYEE VALUES('SAMANTHA', 5000)

INSERT INTO EMPLOYEE VALUES('JERRY', 2000)

INSERT INTO EMPLOYEE VALUES('JOHN', 10000)

SELECT * FROM dbo.EMPLOYEE ORDER BY SALARY DESC

Now in order to get the nth highest salary we can use following query:

DECLARE @nthSalary int = 3 

SELECT * FROM (

SELECT DENSE_RANK() OVER(ORDER BY SALARY DESC)AS SRNO,

EMPNAME,

SALARY

FROM dbo.EMPLOYEE

) T

WHERE T.SRNO = @nthSalary

Here @nthSalary variable contains the highest number of salary we want i.e. second highest, third highest etc.

In above query important point is the use of DENSE_RANK() function. This function gives us the ranking within ordered partition, but the ranks are consecutive. No ranks are skipped if there are ranks with multiple items.

Example: