Geek Answers Handbook

SQL query to find first, second and third highest numbers

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

+4
source share
7 answers

You can sort by value descending and take the top.

SELECT TOP 3 YourVal FROM YourTable ORDER BY YourVal DESC
+9
source

Or, if you want each result to be 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: after OPs comment

to get the nth value, select TOP 1, which is not in TOP (n-1), so the fifth can be selected:

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

The suggested SELECT TOP n ... ORDER BY key will work, but you need to know that you can get unexpected results if the column sorting is not unique. Find more information on the topic here .

+1
source

Sudhakar,

For some of these questions, it might be easier to use ROW_NUMBER () or DENSE_RANK (). For example, to find YourVal and other columns from the fifth row in the order of your VAL DESC object:

 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 excellent YourVal value, just change ROW_NUMBER () to DENSE_RANK ().

One of the very important advantages of these functions is the fact that you can immediately change the query "nth high YourVal" to "nth the highest query YourVal for every other column " by adding PARTITION BY otherColumn for the OVER clause.

+1
source

In some DBMS packages, the top command may not work. Then how to do it? Suppose we need to find the 3rd largest salary in the employee table. Therefore, we select the reported salary from the table in descending order:

 select distinct salary from employee order by salary desc

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

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

This gives the top 3 salaries in ascending order. This makes the third largest salary in the first position. Now we have the last 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 error in the request, 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
+1
source

If you have a table called "Orders" and 3 columns "Identifier", "Product" and "Quantity", then to retrieve the top 3 highest values, your query will look like this:

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

or if you just want a quantity column:

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

It 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 want to get the 2nd, 3rd or 4th maximum, just change top3 to the corresponding number.

0
source

More articles:


All Articles