smerny Posted August 30, 2009 Share Posted August 30, 2009 I have a table with fields ID, ID_user, action, ip, time what I want is to take the rows with the highest ID, one per ID_user... so if the table is like.. ID, ID_user, action, ip, time 1, 1, ... 2, 1, ... 3, 1, ... 4, 3, ... 5, 3, ... 6, 1, ... 7, 2, ... 8, 2, .. it should take the rows 8, 2, ... 6, 1, ... 5, 3, ... Quote Link to comment Share on other sites More sharing options...
kingnutter Posted August 30, 2009 Share Posted August 30, 2009 My first guess would to be use DESC, which gives you the result in descending order with a LIMIT of one. Along the lines of $query="SELECT id_user FROM table ORDER BY DESC LIMIT 1,0"; But check my syntax. Not sure how to put them all in one query though. Quote Link to comment Share on other sites More sharing options...
smerny Posted August 30, 2009 Author Share Posted August 30, 2009 no thats not it... what I tried was "SELECT * FROM tracklog GROUP BY ID_user ORDER BY ID DESC" but the results were like... 7,2, ... 4,3, ... 1,1, ... (it's grouping them into the lowest ID for each ID_user and then sorting those DESC, how do I get it to group them into the highest ID for each ID_user?) Quote Link to comment Share on other sites More sharing options...
smerny Posted August 30, 2009 Author Share Posted August 30, 2009 didn't think i'd have to wait so long for an answer on this.. Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted August 30, 2009 Share Posted August 30, 2009 If you just want the highest ID for each ID_user and don't care about getting the other columns from the row with that highest ID, you can do the following - SELECT max(ID), ID_user, action FROM your_table GROUP BY ID_user The above will give you values for the remaining columns of the first row encountered for each ID_user, not the values from the row with the highest ID for each ID_user. However, if you want to directly retrieve the row that corresponds to the highest ID for each ID_user, you will need to use one of the methods shown at this link - http://mysql.proserve.nl/doc/refman/5.1/en/example-maximum-column-group-row.html Quote Link to comment Share on other sites More sharing options...
smerny Posted August 30, 2009 Author Share Posted August 30, 2009 thanks... ended up using SELECT * FROM tracklog t1 JOIN ( SELECT ID_user, MAX(ID) AS ID FROM tracklog GROUP BY ID_user) AS t2 ON t1.ID_user = t2.ID_user AND t1.ID = t2.ID Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.