CODEWITHSUNDEEP
asp.netsqlsql-server
Sudhakar K
Sudhakar K

Reputation: 29

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

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

Upvotes: 4

Views: 13201

Answers (7)

Irfan Ashraf
Irfan Ashraf

Reputation: 2460

This works prefect!

select top 1 * from Employees where EmpId in 
(
    select top 3 EmpId from Employees order by EmpId
) order by EmpId desc;

If you would like to get 2nd, 3rd or 4th highest just change top3 to appropriate number.

Upvotes: 0

Sandeep
Sandeep

Reputation: 106

In certain DBMS packages the top command may not work. Then how to do this? Suppose we need to find the 3rd largest salary in employee table. So we select the distinct salary from the table in descending order:

select distinct salary from employee order by salary desc

Now among the salaries selected we need top 3 salaries, for that we write:

select salary from (select distinct salary from employee order by salary desc) where rownum<=3 order by salary

This gives top 3 salaries in ascending order. This makes the third largest salary to go to first position. Now we have the final task of printing the 3rd largest number. 

select salary from (select salary from (select distinct salary from employee order by salary desc) where rownum<=3 order by salary) where rownum=1

This gives the third largest number. For any mistake in the query please let me know. Basically to get the nth largest number we can rewrite the above query as

select salary from (select salary from (select distinct salary from employee order by salary desc) where rownum<=**n** order by salary) where rownum=1

Upvotes: 1

Steve Kass
Steve Kass

Reputation: 7184

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.

Upvotes: 1

Kev Riley
Kev Riley

Reputation: 711

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

Upvotes: 2

Darko
Darko

Reputation: 38880

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

Upvotes: 0

The Chairman
The Chairman

Reputation: 7187

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.

Upvotes: 1

pjp
pjp

Reputation: 17639

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

SELECT TOP 3 YourVal FROM YourTable ORDER BY YourVal DESC

Upvotes: 9

Related Questions