SQL SERVER – 2005 – Find Nth Highest Record from Database Table – Using Ranking Function ROW_NUMBER

I have previously written SQL SERVER – 2005 – Find Nth Highest Record from Database Table where I have shown query to find 4th highest record from database table. Everytime when I write blog I am always very eager to read comments of readers. Some of regular readers are industry leaders and and their comments always teach us all something new. One of them is Nicholas Paldino [.NET/C# MVP]. He has always provided valuable solution and comments to this blog. His recent comment about finding Nth Highest Record is quite an interesting.

USE AdventureWorks
GO
SELECT
t.*
FROM
(
SELECT
e1.*,
row_number() OVER (
ORDER BY e1.Rate DESC) AS _Rank
FROM
HumanResources.EmployeePayHistory AS e1
) AS t
WHERE
t._Rank = 4

Reference : Pinal Dave (https://blog.sqlauthority.com) , Nicholas Paldino [.NET/C# MVP]

, , ,
Previous Post
SQL SERVER – How to Retrieve TOP and BOTTOM Rows Together using T-SQL – Part 3
Next Post
SQL SERVER – FIX : Error : 3702 Cannot drop database because it is currently in use – Part 2

Related Posts

24 Comments. Leave new

  • Yes, row_number() is quite useful in these cases, and during pagination

    but it is a performance drag as well, especially for pagination
    imagine assigning row # to millions of records, and only need record 50~100 for page 2

    Reply
  • Also see what you can do with row_number() function

    And many methods to find Nth Maximum value

    Reply
  • But if the records are duplicated i dont think it will return the correct result ??

    Reply
  • Very nice approach Nicholas.
    I used the same query to gain little bit performance when we have to have the nth value out of very long list. This can give some performance boost. The inner query will give only the top results from which the top most row will be displayed

    USE AdventureWorks
    GO
    declare @nthVal int
    set @nthVal=4–the required nth value

    SELECT top 1 t.*
    FROM
    (
    SELECT top (@nthVal) e1.*,row_number() OVER (ORDER BY e1.Rate DESC) AS _Rank
    FROM HumanResources.EmployeePayHistory AS e1
    ) AS t
    order by t._Rank desc

    Reply
  • owsem man.. this site has helped me very weill with my interview.

    keep it up

    Reply
  • @ Jerry Hung

    Can you give some more information on how row_number() will reduce performance?

    Thanks in advance.

    Reply
  • how do we find positions of the record from a table using SQL SERVER 2000? for example

    table name : employee

    EMP_NAME AGE SALARY
    ———————————–
    aaa 30 2000.00
    abc 20 5000.00
    bbb 40 3000.00
    bbb 60 3000.00
    ccc 50 4000.00
    xyz 25 1000.00

    there is no primary key in this table.

    In this table i want to find out 3rd record. i.e.
    bbb 40 3000.00

    Reply
  • We can write this query like this also

    Select * from table name order by desc column name lmit1,1

    Reply
  • Hi All,

    I want to do pagination using SQL, i having MS SQL 2000. It does not support row_number(). How can i do pagination !!

    Please Help !!

    Thanks on Adv.

    Rocky R

    Reply
  • Hi All,

    I want to Pagination using SQL. I having db Ms Sql 2000.
    Please help on this front.

    Thanks in Adv.

    Regards

    Rocky R

    Reply
  • I just need to find the highest value row in a table. How do I do that? I assume it is going to be a simpler command.

    Reply
  • Good Use of RANK() function.

    Reply
  • I want to Pagination using SQL. I having db Ms Sql 2000.
    Please help on this front.

    Thanks in Adv.

    Reply
  • How To Find a Record 3rd From bottom In Database?

    Can anyone help me?

    Reply
  • > Ajith
    >
    > But if the records are duplicated i dont think it will return > the correct result ??

    Use DENSE_RANK() for this situation instead of ROW_NUMBER()

    Reply
  • Am I too late?

    Try this..

    SELECT TOP 1 ID,Name
    FROM
    (SELECT TOP 6
    ID, Name
    FROM Table
    ORDER BY ID ASC) as top6
    ORDER BY ID Desc

    Reply
  • Hi All,

    Is it possible that……..

    Find Nth Highest Record from Database Table without using “TOP” and “ORDER BY”???

    Reply
  • Reply
  • [last reply was a mistake]

    @Dinesh

    Using ROW_NUMBER() as in the article does not use the normal ORDER BY, that ORDER BY is part of the ROW_NUMBER syntax.

    There is a way to do it without even that, but it is not very efficient. Bascially, use MAX() and WHERE <.

    For example, to find the third highest id:

    SELECT MAX(id) FROM mytab WHERE id < (SELECT MAX(id) FROM mytab WHERE id < (SELECT MAX(id) FROM mytab))

    This can be done with a join as well.

    Reply
  • @ Brian Tkatch
    thank you so much for the info
    you’ve help me a lot…

    Reply

Leave a Reply

Menu