How to get the highest values ​​in SQL?

Question

For example, I want to know who are the tallest guys in any group of people. I use

SELECT name, height FROM people WHERE height = (SELECT MAX(height) FROM people) 

But it doesn’t look very good. What is the best way to do this?

Answer 1

If you are using SQL Server, then

 SELECT TOP 1 name, height FROM people ORDER BY height DESC 

SQL Server does not support the LIMIT method, so here you need to use TOP

So if you are using MySQL:

 SELECT name, height FROM people ORDER BY height DESC LIMIT 1 

OR

If you are concerned about more than 1 person with the highest height, you can use the subquery:

 SELECT name, height FROM people WHERE height = (SELECT TOP 1 height FROM people ORDER BY height DESC) ORDER BY name 

And, of course, the same example with MySQL:

 SELECT name, height FROM people WHERE height = (SELECT height FROM people ORDER BY height DESC LIMIT 1) ORDER BY name 

Answer 2

You can use LIMIT (MySQL) OR top (MsSQL):

 SELECT name, height FROM people ORDER BY height DESC LIMIT 1; SELECT TOP 1 name, height FROM people ORDER BY height DESC; 

This will result in 1 entry.

To get more records, your subquery is working fine. If you do not like subqueries, you can use these queries (MySQL):

 SELECT MAX(height) INTO @maxHeight FROM person ORDER BY height DESC LIMIT 1; SELECT * FROM person WHERE height = @maxHeight; 

This way you can reuse @maxHeight in other requests (for the current connection).

Answer 3

Assuming you are using MySQL:

 SELECT name, height FROM people ORDER BY ABS(height) DESC LIMIT 1