Post reply

Retrieving a nth highest row

  • September 4, 2009 at 4:36 am

    #210290

    I have a table Personnel with Two Columns ID and Salary I want to retrieve the record with nth Highest salary.

    how to go about this?

    Thanks in advance

  • September 4, 2009 at 4:40 am

    #1049288

    You can use Row_Number.

    If you want more specific help, please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 4, 2009 at 4:46 am

    #1049289

    How can i use row_number the 5th row need not neccessarily be the 5th highest salary??

    Consider This

    ID SALARY

    == =======

    1 1000

    2 6000

    3 4000

    4 2000

    5 6000

    6 1000

    In this I want to first fetch the row(s) with third highest salary( that is 2000)

  • September 4, 2009 at 4:53 am

    #1049294

    with cteSalary(Id,Salary,RowN)

    as

    (

    Select Id,Salary,row_number() over (order by salary)

    )

    Select * from cteSalary where Rown = 3


    Clear Sky SQL
    My Blog[/url]

  • September 4, 2009 at 5:41 am

    #1049310

    Thanx mate!!!!

  • September 4, 2009 at 6:00 am

    #1049314

    I think DENSE_RANK would be useful to you for your requirements, which will return records that have the same Salary, note I have also changed the order by to descending as you specified you need the nth highest salary.

    WITH Salaries (ID, Salary, Ranking) AS

    (

    SELECT ID, Salary, DENSE_RANK() OVER(ORDER BY Salary DESC)

    FROM [Table]

    )

    SELECT ID, Salary FROM Salaries WHERE Ranking = N

    Dave

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply