SQL Server -Find Nth Highest Salary

Problem: Find Nth highest salary in Minimal time.

Solution: Interviewer often asks to find Nth Highest Salary in an interview. Many solutions are available on internet to do so. Couple of commonly used solutions are Solution 1 & Solution 2.

All these solutions are bit slow. But by using Common Expression Tables (CTE) you can find the Nth highest salary very efficiently and impress your interviewer.

• Create a Sample Table and  Insert Sample Data in it:

CREATE TABLE #T1 (ID1 INT, [Name] NVARCHAR(50), Salary INT)

After creating the table, execute the above code to insert the data into the table as follows:

INSERT INTO #T1 VALUES (1, ‘pds’, 10000)
INSERT INTO #T1 VALUES (2, ‘vicky’, 10000)
INSERT INTO #T1 VALUES (3, ‘divyan’, 30000)
INSERT INTO #T1 VALUES (4, ‘deepak’, 40000)
INSERT INTO #T1 VALUES (5, ‘anu’, 50000)
INSERT INTO #T1 VALUES (6, ‘jai’, 60000)
INSERT INTO #T1 VALUES (7, ‘abhi’, 20000)
INSERT INTO #T1 VALUES (8, ‘disha’, 40000)
INSERT INTO #T1 VALUES (9, ‘anika’, 60000)
INSERT INTO #T1 VALUES (10, ‘vijay’, 50000)

To find the highest salary we can simply use the Max function as given below:

Select Max(Salary) as [Highest Salary] from T1

• Following Query will Find Nth Highest Salary in minimal time:

         declare @n int;

        set @n =4

        with cte as

       (

          SELECT name, salary,
                     dense_rank() OVER( ORDER BY salary DESC) AS rowid
                   FROM   #t1
              )

     SELECT *
    FROM   cte
    WHERE  rowid = @n

     This will create row number for each row after it has been sorted by salary in the descending order.

• Comparison Stats with Other Solutions: