Order By Before Group By

MySQL & MariaDB Other MySQL® Questions
1

I’m trying to GROUP BY a column and have the row with the latest timestamp to be returned. Example:

TABLE Test

id | text | timestamp
1 , ‘A’ , 2008-03-01
1 , ‘B’ , 2008-03-26

SELECT *
FROM Test
GROUP BY id

RESULTS:
1 , ‘A’ , 2008-03-01

I need the results to be the row with the latest timestamp:
1 , ‘B’ , 2008-03-26

I know i can nest queries to do this but this will result in temporary tables which will not be optimized. Is it possible to Order By before the Group or get this result using another method?

Thanks

2

This is what you want:

SELECT id, MAX(timestamp)FROM TestGROUP BY id

Read about the group by functions here:
http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.ht ml

3

Thanks for the reply but that will not work as it will not return the row with the greatest timestamp only the greatest timestamp for all the rows.

I did find the answer i was seeking at MySQL. This type of query is referred to as a Group-wise Maximum query.

http://dev.mysql.com/doc/refman/5.0/en/example-maximum-colum n-group-row.html

In Google Groups some have labelled it a Strawberry Query.

4

hi jadent send that code

5

I know it’s a bit late, but still useful for newcomers.

If there’s a PK or a UNIQUE constraint (on 1 or many fields), that may work :

SELECT Test.*FROM TestINNER JOIN ( SELECT MAX(id) id FROM Test GROUP BY gid) s ON s.id = Test.id

… where id is PK of table ‘Test’ and gid, the field to group by.