Data World

SQL Server -Find Nth Highest Salary

Posted by Pramod Singla on August 15, 2014


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:

Advertisements

4 Responses to “SQL Server -Find Nth Highest Salary”

  1. […] How to find the third highest salary for an employee?Ans […]

  2. Adrian said

    Thank you so much for this post – very interesting SQL query!

    This page also helped my understanding and wanted to share:

    http://www.programmerinterview.com/index.php/database-sql/find-nth-highest-salary-sql/

  3. I like this post, enjoyed this one thankyou for posting .

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: