Generic way in MySQL to get the top results

Question

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?

Answer 1

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)

Answer 2

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);