I have a table with the following. The names are not unique.
userid, name
1, dave
2, john
3, mike
4, mike
5, dave
I want to return the unique name with the highest userid.
ie.
2, john
4, mike
5, dave
What is the query to do so?
views:
52answers:
2
A:
select userid, name from users where name='john' order by userid desc limit 1
or
select userid, name from users where userid
= (select max(userid) from users where name='john' )
The first syntax is MySQL-specific (no "limit" clause in other databases).
Seva Alekseyev
2010-01-07 22:04:39
these queries only return one row which is not correct
Yada
2010-01-07 22:08:57
not marking you down here, but your first statement will only return one row (not all three as described in the question) and your second will only return the names with the highest ids (which could be more than one in the case of ties).
davek
2010-01-07 22:09:52
Sorry, the question was unclear. See davek's answer then.
Seva Alekseyev
2010-01-07 22:10:55
+6
A:
select name, max(userid) as max_userid
from users
group by name
order by max(userid) asc
davek
2010-01-07 22:08:23