donchen Posted May 26, 2009 Share Posted May 26, 2009 Sample table ---------------- Username Datetime Data User1 26/05/09 11.30 AAAA User1 26/05/09 11.55 BBBB User1 26/05/09 14.30 CCCC User2 26/05/09 09.45 DDDD User2 26/05/09 10.53 EEEE User2 26/05/09 15.50 FFFF --------------------------------------------------------------------------------- Using the above table, I want to select the LATEST DATA according to Datetime for EACH USER. So using the above table, the result will be: User1 26/05/09 14.30 CCCC and User2 26/05/09 15.50 FFFF Anyone can help construct the statement? Thanks! Link to comment Share on other sites More sharing options...
0 scumdogmillionaire Posted May 26, 2009 Share Posted May 26, 2009 You'll want to use GROUP BY for this and use max to get the latest date. SELECT Username, MAX(Datetime) FROM Sample GROUP BY Username Link to comment Share on other sites More sharing options...
0 donchen Posted May 26, 2009 Author Share Posted May 26, 2009 Using the above doesn't give me the DATA column and if i use SELECT data as well, i get some error saying data is not included in GROUP BY clause. Link to comment Share on other sites More sharing options...
0 Kosh Posted May 26, 2009 Share Posted May 26, 2009 SELECT Username, MAX(Datetime), Data FROM Sample GROUP BY Username, Data That should do the trick. Link to comment Share on other sites More sharing options...
0 donchen Posted May 26, 2009 Author Share Posted May 26, 2009 Kosh, it doesn't work. It gives me all entries. Link to comment Share on other sites More sharing options...
0 Rollie Posted May 26, 2009 Share Posted May 26, 2009 Not so sure if it's fast for large amounts of data, but here is my idea: SELECT * FROM tbltest tt WHERE datetime = ( SELECT MAX( datetime ) FROM tbltest WHERE tt.username = username ) ORDER BY datetime DESC Link to comment Share on other sites More sharing options...
0 donchen Posted May 26, 2009 Author Share Posted May 26, 2009 Yeah! That works fine! thanks! Link to comment Share on other sites More sharing options...
0 scumdogmillionaire Posted May 26, 2009 Share Posted May 26, 2009 Not so sure if it's fast for large amounts of data, but here is my idea:SELECT * FROM tbltest tt WHERE datetime = ( SELECT MAX( datetime ) FROM tbltest WHERE tt.username = username ) ORDER BY datetime DESC I would imagine this would take a performance hit. Perfectly acceptable solution though. Not sure why the group by didn't work for you. Link to comment Share on other sites More sharing options...
0 donchen Posted May 27, 2009 Author Share Posted May 27, 2009 Could it elaborate on why will it take a performance hit. I am pretty concern because in time to come, my database might grow huge. Link to comment Share on other sites More sharing options...
0 Rollie Posted May 28, 2009 Share Posted May 28, 2009 What I'd do, is fill up a test table with, let's say 10.000 records, and see a plan of the query, and see whether you can add/modify indexes. Makes a huge difference. If you are using MySQL, you can use 'EXPLAIN' ( EXPLAIN SELECT * FROM tbltest....) and see there how many rows are accessed. I guess I'd index `username`. But I'm not known as making great db structures :) Link to comment Share on other sites More sharing options...
Question
donchen
Sample table
----------------
Username Datetime Data
User1 26/05/09 11.30 AAAA
User1 26/05/09 11.55 BBBB
User1 26/05/09 14.30 CCCC
User2 26/05/09 09.45 DDDD
User2 26/05/09 10.53 EEEE
User2 26/05/09 15.50 FFFF
---------------------------------------------------------------------------------
Using the above table, I want to select the LATEST DATA according to Datetime for EACH USER.
So using the above table, the result will be:
User1 26/05/09 14.30 CCCC
and
User2 26/05/09 15.50 FFFF
Anyone can help construct the statement? Thanks!
Link to comment
Share on other sites
9 answers to this question
Recommended Posts