• 0

[SQL] How to perform this query?


Question

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

  • 0

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

  • 0

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

  • 0

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

  • 0
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

  • 0

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

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.