SQL SERVER – Find Nth Highest Record from Database Table

I had previously written SQL SERVER – Query to Retrieve the Nth Maximum value. I just received an email that if I can write this using AdventureWorks database as it is a default sample database for SQL Server 2005 and the user can run the query against it and understand it better. Let us see how we can find highest record from database.

SQL SERVER - Find Nth Highest Record from Database Table nthhigh-800x291

Here is query to find 4th Highest Record from Database Table.

USE AdventureWorks;
GO
SELECT *
FROM HumanResources.EmployeePayHistory E1
WHERE (4-1) = (SELECT COUNT(DISTINCT(E2.Rate))
FROM HumanResources.EmployeePayHistory E2
WHERE E2.Rate > E1.Rate)
GO

Here is the result set of the above query which retrieves 4th highest record from the database table.

SQL SERVER - Find Nth Highest Record from Database Table 4thhighest

In above query where you see (4-1), you can replace the 4 with any other number based on your need. For example, if you want to find the 10th highest record, you can just write (10-1) or just 9 there and it will bring back necessary record. It is a very easy query and requires one time proper understanding how it works.

Let me know if you have any question in the comments section. I keep on sharing the various tips, I suggest you sign up for my newsletter http://go.sqlauthority.com

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQLAuthority News – 500th Post – An Interesting Journey with SQL Server
Next Post
SQLAuthority News – Authors Most Visited Article on Blog

Related Posts

32 Comments. Leave new

  • How can I calculate monthly income of the employees in the adventure works

    Reply
  • Manu Vashishtha
    July 2, 2013 12:20 pm

    Please explain How this Query is accessing the data from database????
    How it is working?

    Reply

Leave a Reply

Menu