CODEWITHSUNDEEP
sqlmysql
Chris Lloyd
Chris Lloyd

Reputation: 12308

Generic way in MySQL to get the top results

Say for instance I have a table which has names of people and their ages. I want a generic query in MySQL which will pull out the n oldest people.

I could use something like this:

SELECT * FROM people ORDER BY age DESC LIMIT 1;

Say Frank and Emily are both 99 years old. The limit 1 will only return one of their names. In psuedo-sql I would want the query to look like this:

SELECT * FROM people WHERE MAX(age);

I know you can also do this with a subquery, but there must be an easier, more generic way of achieving this?

Upvotes: 1

Views: 1017

Answers (3)

smahesh
smahesh

Reputation: 663

@cletus - 

SELECT * 
FROM people 
WHERE age IN (SELECT DISTINCT age 
              FROM people 
              ORDER BY age DESC LIMIT 3)

LIMIT does not work in a sub query in MySQL.

Upvotes: 0

cletus
cletus

Reputation: 625267

Nope, subquery is it. For example, everyone with the top 3 ages:

SELECT * FROM people WHERE age IN (SELECT DISTINCT age FROM people ORDER BY age DESC LIMIT 3)

or just the top age:

SELECT * FROM people WHERE age = (SELECT MAX(age) FROM people)

Upvotes: 2

tpdi
tpdi

Reputation: 35171

I know you can also do this with a subquery, but there must be an easier, more generic way of achieving this?

Not in ANSI SQL.

SELECT * FROM people WHERE age = 
  (select MAX(age) from people);

Upvotes: 1

Related Questions