Select first row for each different column value

Question

Having a table T like this:

 ID    DateTime   asha   
AF35   15-01-17    af   
AF35   14-01-17    la   
AF35   13-01-17    fi   
DF57   15-01-17    sk   
DF57   14-01-17    sj   
DG36   15-01-17    be   
DG36   14-01-17    dh

What is the simplest mysql query to have only first row for each unique ID returned, being ordered by the most recent DateTime?

I the expected result would be something like this:

ID    DateTime   asha   
AF35   15-01-17    af     
DF57   15-01-17    sk    
DG36   15-01-17    be

I tried SELECT * FROM T GROUP BY ID ORDER BY DateTime, however, mysql returns an error for not adding the other columns to the clause. And if I add them, I still get duplicated results for the ID.

ie from dupe: `select * from (select * from t order by DateTime desc) group by ID`, but you have to set `sql-mode` to *not* include `ONLY_FULL_GROUP_BY`for this to work. — Bohemian, Mar 03 '17 at 19:05
Wildly upvoted as it is, I'm afraid that the accepted answer at the linked page relies on an undocumented hack. That said, there are other answers which *are* documented. — Strawberry, Mar 03 '17 at 19:15

Anand · Accepted Answer · 2017-03-03T19:04:45.997

6

My favorite method to write this query is with a not exists clause:

select *
from T as t1
where not exists (
    select 1 
    from T as t2 
    where t2.ID = t1.ID 
    and t2.DateTime > t1.DateTime
)

edited Mar 03 '17 at 19:04

answered Mar 03 '17 at 18:56

Anand

1,165
10
18

Nicely done, I missed the different asha earlier. – SqlZim Mar 03 '17 at 18:58
Thank you! @SqlZim, means a lot coming from a guru :) – Anand Mar 03 '17 at 18:59
I don't understand how this could work. Wouldn't `select 1 from T as t2` simply return a column named 1 with a bunch of 1's for every row? how could the where clause work with t2.ID if t2 has no column named ID? – lemur Mar 03 '17 at 19:37
@mtsfaria, I can see your confusion :) I was there once. The table t2 DOES have the ID column. It is simply an alias for the T table. What I've done here is join the T table with itself on the ID column and if there are any rows where t2 has a later DateTime, it would get excluded because the whole thing is inside a NOT EXISTS clause leaving only the rows in t1 where the DateTime is the latest. The ```select 1``` either returns 1 or not as you observed. You might as well use ```select "blah"``` instead of 1 and you would get the same results. – Anand Mar 03 '17 at 19:45
Thanks Anand! I understood the technique and applied it sucessfully to my needs. – lemur Mar 06 '17 at 13:29
Great answer actually! Also impressed with Google to point me straight to this question when my search query was absolutely unrelated!! – Prasannjeet Singh Jan 24 '23 at 14:53

score -1 · Answer 2 · answered Mar 03 '17 at 19:00

-1

SELECT Distinct(ID),DateTime,asha FROM T GROUP BY ID ORDER BY DateTime desc

use above query to unique Id records.

answered Mar 03 '17 at 19:00

Manohar Godase

109
4

DISTINCT is not a function. And this query returns indeterminate results. Bad. – Strawberry Mar 03 '17 at 19:16

Select first row for each different column value

2 Answers2