Selecting all people with the max age?

问题:

I want to get values from a row with the largest certain value (in this example, the oldest member)

Select * from members where age=max(age)

Will this work?

And what will happen if there is more than 1 oldest members with the same age?

(I don't have to worry about it because I use auto_increment, but I just got curious)

Thanks.

回答1:

select * 
from members 
where age = (select max(age) as max_age from members);

If there are more than 1 member with the same maximum age, you will get multiple results. To select just one from that:

select * 
from members 
where age = (select max(age) as max_age from members);
limit 1

You can optionally add an order by if you favor any particular data over a random one.

回答2:

This will not work since this is Invalid use of group function, better use SELECT * FROM members ORDER BY age DESC LIMIT 1

回答3:

No.

You want

 Select * from Members 
 where Age = (Select Max(Age) from Members)

If, as suggested by your mention of autoincrement, you are looking for the last inserted ID in a table, you want

 Select LAST_INSERT_ID()

回答4:

It can be done with a single SELECT statement, if you use GROUP BY and a @variable to find and store what is min/max value and filter matches with HAVING at the end.

Presuming you have a table members with at least two columns: id and age, then you can use a statements like this:

SELECT id, age, @max:= IF(@max > age, @max, age) FROM members GROUP BY id HAVING(age = @max);

or

SELECT id, age, @min:= IF(@min < age, @min, age) FROM members GROUP BY id HAVING(age = @min);

回答5:

This will return only one row - even if more than one members have the same maximum age:

SELECT *
FROM members
ORDER BY age DESC
LIMIT 1 ;

This (and @podiluska's answer) will return all of them:

SELECT m.*
FROM members AS m
  JOIN
    ( SELECT MAX(age) AS max_age
      FROM members
    ) AS allm
    ON allm.max_age = m.age ;

回答6:

There's also a more contrived way of doing the same:

SELECT * FROM members m
WHERE NOT EXISTS (
    SELECT 1 FROM members m2
    WHERE m1.age < m2.age )

This query is portable to most databases, works for auto-increasing PKs and also with multiple people having same age. Plus, at least in SQL Server, most of the times will be faster than the version using a subquery (especially if [members] has an index over column [age]).