hi

i have a mysql probelm which has me stumped, i wonder if one of you clever people can help.

i have 2 tables - user (contains user's info) and logins(creates a new row everytime a user logs in detailing ip, date, login count etc).

Id like to run a query which displays user info ordered by most recent login, my problem is that when i join these 2 tables together, i get every row created by by the user from the logins table, and i only need to show the most recent one accosiated with userid from the user table.

<!--example user table -->
id username
1 bob
2 sally
3 sarah

<!--example login table -->
id login
1 2004-06-05
3 2005-04-01
2 2006-01-01
3 2007-11-02
2 2007-10-05
2 2007-09-15

<!-- results id like to see -->
id username login
2 sally 2007-09-15
3 sarah 2007-11-02
1 bob 2004-06-05

the query would be along the lines of -

select * from user left join logins on user.userid=(logins.userid where logins.login=most recent) order by logins.login

i hope that this makes sense, i dont think its a particularly hard query but i cant think through it.

thanks in advance

SELECT u.id, u.username, MAX(l.login) FROM user AS u LEFT JOIN login AS l ON u.id = l.id GROUP BY l.id ORDER BY l.login DESC

If the query is working add
limit 1
or if that is the first login
desc limit 1

excellent! thanks very much chrishirst, its about time a learned what grouping was for.