SQL query for finding the first, second and third highest numbers

Question

What is an example query to retrieve the first, second and third largest number from a database table using SQL Server?

Answer 1

You can sort by your value descendingly and take the top 3.

SELECT TOP 3 YourVal FROM YourTable ORDER BY YourVal DESC

Answer 2

If you have a table called Orders and 3 columns Id, ProductId and Quantity then to retrieve the top 3 highest quantities your query would look like:

SELECT TOP 3 [Id], [ProductId], [Quantity] FROM [Orders] ORDER BY [Quantity] DESC

or if you just want the quantity column:

SELECT TOP 3 [Quantity] FROM [Orders] ORDER BY [Quantity] DESC

Answer 3

Or if you wanted each result separate,

first number :

SELECT TOP 1 YourVal FROM YourTable ORDER BY YourVal DESC

second number:

SELECT TOP 1 YourVal FROM YourTable 
WHERE YourVal not in (SELECT TOP 1 YourVal FROM YourTable ORDER BY YourVal DESC)
ORDER BY YourVal DESC

third number:

SELECT TOP 1 YourVal FROM YourTable 
WHERE YourVal not in (SELECT TOP 2 YourVal FROM YourTable ORDER BY YourVal DESC)
ORDER BY YourVal DESC

assuming YourVal is unique

---

EDIT : following on from OPs comment

to get the nth value, select the TOP 1 that isn't in the TOP (n-1), so fifth can be chosen by:

SELECT TOP 1 YourVal FROM YourTable 
WHERE YourVal not in (SELECT TOP 4 YourVal FROM YourTable ORDER BY YourVal DESC)
ORDER BY YourVal DESC

Answer 4

The proposed SELECT TOP n ... ORDER BY key will work but you need to be aware of the fact that you might get unexpected results if the column you're sorting on is not unique. Find more information on the topic here.

Answer 5

Sudhakar,

It may be easier to use ROW_NUMBER() or DENSE_RANK() for some of these questions. For example, to find YourVal and other columns from the fifth row in order of YourVal DESC:

WITH TRanked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      ORDER BY YourVal DESC, yourPrimaryKey
    ) AS rk
)
  SELECT YourVal, otherColumns
  FROM TRanked
  WHERE rk = 5;

If you want all rows with the fifth largest distinct YourVal value, just change ROW_NUMBER() to DENSE_RANK().

One really big advantage to these functions is the fact that you can immediately change a "the nth highest YourVal" query to a "the nth highest YourVal for each otherColumn" query just by adding PARTITION BY otherColumn to the OVER clause.