Different ways to find the Nth max salary in SQL Server

It is the most common interview question to be asked for SQL developers. Finding Nth highest value/salary in a table, where N could be 2, 3, 4 or anything.

In order to solve this problem, you need to know generic logic about some T-SQL concepts like CTE, Cross Apply, Correlated Sub Query, Ranking functions.
In this article, various ways to find the Nth highest value will be discussed.

Consider the following table:

CREATE TABLE EMPLOYEE(EID INT,ESAL FLOAT)
INSERT INTO EMPLOYEE VALUES (1,15000)
INSERT INTO EMPLOYEE VALUES (2,10000)
INSERT INTO EMPLOYEE VALUES (3,25000)
INSERT INTO EMPLOYEE VALUES (4,18000)
INSERT INTO EMPLOYEE VALUES (5,12000)
INSERT INTO EMPLOYEE VALUES (6,5000)
INSERT INTO EMPLOYEE VALUES (7,9000)
INSERT INTO EMPLOYEE VALUES (8,50000)
INSERT INTO EMPLOYEE VALUES (9,35000)
INSERT INTO EMPLOYEE VALUES (10,21000)

In the below examples, I am considering N = 5, mean finding the 5th highest value.
Using Ranking Functions
ROW_NUMBER

SELECT ESAL
FROM 
    (SELECT DISTINCT ROW_NUMBER()
        OVER (ORDER BY ESAL DESC) ESAL_RNK,ESAL
    FROM EMPLOYEE EMP) RS1
WHERE RS1.ESAL_RNK=5

RANK

SELECT ESAL
FROM 
    (SELECT DISTINCT RANK()
        OVER (ORDER BY ESAL DESC) ESAL_RNK,ESAL
    FROM EMPLOYEE EMP) RS1
WHERE RS1.ESAL_RNK=5

DENSE_RANK

SELECT ESAL
FROM 
    (SELECT DENSE_RANK()
        OVER (ORDER BY ESAL DESC) ESAL_RNK,ESAL
    FROM EMPLOYEE EMP) RS1
WHERE RS1.ESAL_RNK=5

Here, In select statement DISTINCT keyword is not using because DENSE_RANK doesn't have gaps in the rank values.

Using TOP Keyword

SELECT TOP(1) ESAL
FROM 
    (SELECT DISTINCT TOP(5) ESAL
    FROM EMPLOYEE EMP
    ORDER BY  ESAL DESC) RS1
ORDER BY  RS1.ESAL

Using CTE (Common Table Expression)

;WITH CTE_NTH_SAL AS 
    (SELECT FIRST_VALUE(ESAL) OVER(ORDER BY ESAL DESC) AS ESAL,
        1 AS ID
    FROM EMPLOYEE
    UNION
    ALLSELECT FIRST_VALUE(EMP.ESAL) OVER(ORDER BY EMP.ESAL DESC) AS ESAL,
        ID
    FROM EMPLOYEE EMP,
        (SELECT ESAL,
        ID+1 AS ID
        FROM CTE_NTH_SAL) CTE_NTH_SAL
        WHERE EMP.ESAL<CTE_NTH_SAL.ESAL
                AND CTE_NTH_SAL.ID<=5 )
    SELECT DISTINCT ESAL
FROM CTE_NTH_SAL
WHERE ID=5

Query cost for this query will be high compared to other

Using Correlated Sub-Query

SELECT ESAL
FROM EMPLOYEE RS1
WHERE 5-1 = 
    (SELECT COUNT(DISTINCT ESAL)
    FROM EMPLOYEE
    WHERE ESAL>RS1.ESAL)

Using Cross Apply

SELECT ESAL
FROM EMPLOYEE RS1 CROSS APPLY 
    (SELECT COUNT(DISTINCT ESAL) AS ESAL_CNT
    FROM EMPLOYEE
    WHERE ESAL>RS1.ESAL) RS2
WHERE 5-1=RS2.ESAL_CNT

That's all about finding Nth highest value in SQL Server.
If you want to calculate 2nd highest, then normal subquery is the best option.

SELECT MAX(ESAL)
FROM EMPLOYEE
WHERE ESAL < 
    (SELECT MAX(ESAL)
    FROM EMPLOYEE)

Let me know if you have any questions, suggestions or comments.