Had my brain been in gear, I would have typed the 'AND Latest.TimeStamp = S.TimeStamp' which you will also need on that join..
Cheers, Matt -----Original Message----- From: Matt Chatterley [mailto:[EMAIL PROTECTED] Sent: 20 March 2004 19:51 To: 'motorpsychkill'; 'mysql' Subject: RE: retrieving last record for all distinct users Making the assumption that you are running a version of MySQL which supports subqueries, I believe you could use: SELECT Login, TimeStamp, IP FROM Sessions S INNER JOIN ( SELECT MAX(TimeStamp) TimeStamp, Login FROM Sessions GROUP BY Login ) Latest ON Latest.Login = S.Login Or something very similar - using a subquery (and joining to it), to ensure you only look at the latest records. I've made the assumption that 'Login' is your way to uniquely identify a user! Thanks, Matt -----Original Message----- From: motorpsychkill [mailto:[EMAIL PROTECTED] Sent: 20 March 2004 01:18 To: mysql Subject: retrieving last record for all distinct users I have a table SESSIONS with the following fields: SESSION_ID LOGIN IP TIMESTAMP I am trying to select the last login record for all distinct users. The closest I can get to is: select distinct LOGIN, TIMESTAMP, IP from SESSIONS group by LOGIN order by TIMESTAMP desc This kind of works but it does not get the correct IP for the last TIMESTAMP. This seems easy enough, but I can't seem to figure this one out today. Can anybody see what I'm missing? Thanks! -m -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
