Selecting all people with the max age?

Question

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.

Solution 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.

Solution 2

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

Solution 3

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

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

View more solutions

Comments

[vearutop]

Table names are case sensitive.

[ypercubeᵀᴹ]

@vearutop: That depends on the settings.