SQL Server Performance

finding fifth largest salary from employee table

Discussion in 'SQL Server 2005 General Developer Questions' started by shankbond, Apr 7, 2010.

  1. shankbond New Member

    Hi,
    I am having a newbie question here which was asked in an interview:
    How to find the fifth largest salary from an employee table where the salary column is of numeric type:
    Select Salary from employee Group By (salary)
    The above query will give me all distinct salaries.
    Max() will give me the highest salary.
    Please reply.
    shankbond, Apr 7, 2010
    #2

  2. FrankKalis Moderator

    Since you posted this in the 2005 forum section, have a look at ROW_NUMBER(). Quite handy for these kind of problems.
    FrankKalis, Apr 7, 2010
    #3

  3. kboodu New Member

    When I've had to solve this, I've found building a view on the base information and then selecting from that worked.
    In this case, you could build a view on the top 5 salaries ( CREATE VIEW vwTop5 AS SELECT TOP 5 salary FROM salaries ORDER BY salary) and then use the following SQL statement (SELECT Max (salary) FROM vwTop5 ) to get the last element.
    I haven't found a better way to do it, so maybe someone else can come up with a one-line response.
    Tim
    kb0odu
    kboodu, Apr 7, 2010
    #4

  4. shankbond New Member

    [quote user="kboodu"]In this case, you could build a view on the top 5 salaries ( CREATE VIEW vwTop5 AS SELECT TOP 5 salary FROM salaries ORDER BY salary) and then use the following SQL statement (SELECT Max (salary) FROM vwTop5 ) to get the last element.[/quote]
    Hi kboodu,
    I think I should be having order by DESC here
    Does order by work for numeric column? (I think it works for varchar or Nvarchar but won't return correct values for numeric , int, float........) can someone clarify on this, am I correct.
    shankbond, Apr 7, 2010
    #5

  5. kboodu New Member

    Order by works for any numeric (smallest to largest for ascending) or date column (earliest to latest). For text, it uses dictionary case order (as specified by your colation).
    You'll have to look to see where you need min/max. I did this off the top of my head. You may need to use the MIN function to get the smallest amount.
    Tim
    kboodu, Apr 7, 2010
    #6

  6. FrankKalis Moderator

    Again, thanks to the new ranking functions available from SQL Server 2005 onwards, these kind of problems have become very easy to solve. If you are only interested in the 5th largest salary you can use something like this:
    DECLARE @t TABLE (ename varchar(10), salary int)

    INSERT INTO @t(ename, salary) SELECT 'Bob', 1000
    INSERT INTO @t(ename, salary) SELECT 'Tom', 1100
    INSERT INTO @t(ename, salary) SELECT 'Dick', 1200
    INSERT INTO @t(ename, salary) SELECT 'Harry', 1900
    INSERT INTO @t(ename, salary) SELECT 'Peter', 1200
    INSERT INTO @t(ename, salary) SELECT 'Paul', 2500
    INSERT INTO @t(ename, salary) SELECT 'Mary', 1500
    INSERT INTO @t(ename, salary) SELECT 'Dilbert', 5000

    SELECT
    X.salary
    FROM
    (SELECT
    ROW_NUMBER() OVER (ORDER BY T.salary DESC) AS rn,
    *
    FROM
    @t T) X
    WHERE
    X.rn = 5
    If you are additionally interested in who earns the 5th largest salary somethine like this will work.
    SELECT
    *
    FROM
    (SELECT
    DENSE_RANK() OVER (ORDER BY T.salary DESC) AS rn,
    *
    FROM
    @t T) X
    WHERE
    X.rn = 5
    There is no need for any additional object such as a view in between.
    FrankKalis, Apr 7, 2010
    #7

  7. Madhivanan Moderator

    Madhivanan, Apr 8, 2010
    #8

  8. shankbond New Member

    [quote user="Madhivanan"]
    You can also use many methods
    http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx
    [/quote]
    Hi Madhivanan
    in the second last method ( method 5th)
    Select min(num) from (select top 5 num from @number order by num desc) T
    I think it should be ( in case there are repeated numbers)
    Select min(num) from (select top 5 num from @number group by num order by num desc) T
    the remaining also have the same problem; I find myself incapable of correcting them at the present level of knowledge I have.
    shankbond, Apr 9, 2010
    #9

  9. Madhivanan Moderator

    [quote user="shankbond"]
    [quote user="Madhivanan"]
    You can also use many methods
    http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx
    [/quote]
    Hi Madhivanan
    in the second last method ( method 5th)
    Select min(num) from (select top 5 num from @number order by num desc) T
    I think it should be ( in case there are repeated numbers)
    Select min(num) from (select top 5 num from @number group by num order by num desc) T
    the remaining also have the same problem; I find myself incapable of correcting them at the present level of knowledge I have.
    [/quote]
    Yes. You are correct
    Madhivanan, Apr 12, 2010
    #10

  10. venkatesanj@hcl.in New Member

    venkatesanj@hcl.in, Apr 20, 2010
    #11

  11. shankbond New Member

    [quote user="FrankKalis"]There is no need for any additional object such as a view in between.[/quote]
    Thanks for the solution Frank.
    Also I am thankful to all of the other people who helped me.
    shankbond, Apr 9, 2010
    #12

  12. Adriaan New Member

    You may also want to look at the WITH TIES option for the TOP clause.
    Adriaan, Apr 9, 2010
    #13

Share This Page