How do I specify the order of GROUP BY?

Question

How do I ensure, when I GROUP BY QID, that only the most recent row is returned?

ID, QID, VALUE, TIMESTAMP
45,1,Male,1362044759
58,1,Female,1362045122
59,1,Male,1362045149
60,1,Female,1362045153
82,1,Female,1362045863
83,1,Female,1362045887
92,1,Male,1362046012
101,1,Female, 1362046401

SELECT ID, QID, VALUE, TIMESTAMP FROM table GROUP BY ID

...returns the first row. I can't simply do a LIMIT 1, as this is just an example, there are lots of QIDs in the table, which are all grouped.

Thanks.

When you're using GROUP BY there's no concept of "the most recent row" - **all** rows are grouped together. — Anthony Grist, Feb 28 '13 at 10:56
Yes, but the SQL returns a value, how do I specify which value this is? It's can't be simply random. — ojsglobal, Feb 28 '13 at 10:59
@Arun, I can't do that as this is just an example, the table has lots of QIDs which are all grouped. — ojsglobal, Feb 28 '13 at 11:00
@TheBounder *What SQL?* You haven't posted the query you're using. — Anthony Grist, Feb 28 '13 at 11:00
@TheBounder Check the answer, they should provide enough information to what you asked. By the way, next an example of query would be welcome ;) — araknoid, Feb 28 '13 at 11:12
@Araknoid...what answer? There's not been an answer which works yet, has there?! — ojsglobal, Feb 28 '13 at 11:18

score 1 · Accepted Answer · answered Feb 28 '13 at 10:54

1

I'm assuming here you want the "latest" row for each QID. You would normally use a derived-table subquery to get each QID's latest TIMESTAMP value and then join on that:

SELECT ...
FROM myTable AS t
INNER JOIN (SELECT QID, MAX(`TIMESTAMP`) AS MaxT FROM myTable GROUP BY QID) l
    ON t.QID = l.QID AND l.maxT = t.`TIMESTAMP`

This is also assuming your TIMESTAMP column increases as time goes on.

answered Feb 28 '13 at 10:54

lc.

113,939
20
158
187

This seems to just returns the MAX timestamp, and not the most recent value. – ojsglobal Feb 28 '13 at 11:07

kaspernj · Answer 2 · 2013-02-28T11:05:10.617

0

You could use 'GROUP_CONCAT' in order to extract grouped data.

SELECT GROUP_CONCAT(ID ORDER BY TIMESTAMP DESC) AS latest_id

edited Feb 28 '13 at 11:05

answered Feb 28 '13 at 10:58

kaspernj

1,243
11
16

score 0 · Answer 3 · edited May 23 '17 at 11:43

0

If you want the most recent record returned:

SELECT *
FROM TBL
ORDER BY `TIMESTAMP` DESC
LIMIT 1;

Otherwise, if you want to get the most recent record for each group of QID check this Stack Overflow Post that treat your same problem with optimal solutions.

edited May 23 '17 at 11:43

Community

1
1

answered Feb 28 '13 at 10:58

araknoid

3,065
5
33
35

How do I specify the order of GROUP BY?

3 Answers3