SQL Server Performance

nth highest salary without using TOP and sub-query from the following table

Discussion in 'ALL SQL SERVER QUESTIONS' started by recherche, Apr 27, 2012.

  1. recherche New Member

    Hi SQL Server Experts,

    I want to get the nth highest salary without using TOP and sub-query from the following table.

    USE [master];
    GO
    IF EXISTS(SELECT NAME FROM SYS.DATABASES WHERE NAME = 'empdb')
    DROP DATABASE empdb;
    GO
    CREATE DATABASE empdb;
    GO
    USE [empdb];
    GO
    IF EXISTS(SELECT NAME FROM SYS.TABLES WHERE NAME = 'Employee')
    DROP TABLE dbo.Employee;
    GO
    CREATE TABLE dbo.Employee
    (ID int, Name NVARCHAR(50), Salary Money);
    GO
    INSERT INTO dbo.Employee
    VALUES
    (1, 'A', 10000),
    (2, 'B', 8000),
    (3, 'C', 8000),
    (4, 'D', 6000),
    (5, 'E', 6000),
    (6, 'F', 6000),
    (7, 'G', 5000), --4th Highest Salary
    (8, 'H', 5000),
    (9, 'I', 5000),
    (10, 'J', 5000),
    (11, 'K', 4000),
    (12, 'L', 4000),
    (13, 'M', 3000),
    (14, 'N', 3000),
    (15, 'O', 1000);
    GO

    --Nth Highest Salary
    -- N = 4
    SELECT TOP 1 Salary FROM
    (
    SELECT DISTINCT TOP 4 Salary FROM Employee
    ORDER BY Salary DESC
    )
    A ORDER BY Salary

    The answer I can think of for now is:

    WITH Salaries AS
    (
    SELECT
    Salary, NTILE(10) OVER(ORDER BY Salary DESC) AS 'NTile'
    FROM
    Employee
    )
    SELECT
    Salary
    FROM
    Salaries
    WHERE
    NTile = 5

    Any optimal answer?

    Thanks
  2. FrankKalis Moderator

    Welcome to the forum!
    Is this a real-world problem or some sort of homework asignment?
  3. recherche New Member

    Excuse me, school teacher. The question is for SQL Server experts.
  4. Luis Martin Moderator

    Frank is the second member with more answers. School teacher?, if so I'm a student.
  5. recherche New Member

    Thought this forum was about SQL Server Q&A. Don't know why the replies are so "pedagogical". Anywise, I am just a learner who wanted to hear it straight from the horse's mouth, I mean the SQL Server experts!
  6. recherche New Member

    I guess the answer is
    ;WITH CTE AS
    (
    SELECT Salary, rnk=DENSE_RANK() OVER (ORDER BY Salary DESC) FROM Employee
    )
    SELECT DISTINCT Salary FROM CTE WHERE rnk=5

    Is there any other optimal answer?
  7. FrankKalis Moderator

    It's amazing what you get when you do a little bit of investigation on your own, isn't it...? ;)
    Anyway, to avoid the DISTINCT in your solution, I would probably change this to a TOP 1, since you know that all the values in the bucket have to be identical anyway, so any value in the result set is just as good as any other value and TOP 1 just grabs one of them, while DISTINCT does imply a sort and therefore might be more costly without any obvious benefit.
  8. recherche New Member

    Thanks for the input :).

Share This Page