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 (http://blog.SQLAuthority.com) , Nicholas Paldino [.NET/C# MVP]

About these ads

25 thoughts on “SQL SERVER – 2005 – Find Nth Highest Record from Database Table – Using Ranking Function ROW_NUMBER

  1. 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

    Like

  2. 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

    Like

  3. 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

    Like

  4. 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

    Like

  5. > 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()

    Like

  6. [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.

    Like

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #020 | SQL Server Journey with SQL Authority

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