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.

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.

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)