Select first row for each different column value [duplicate]

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_BYfor this to work. — Bohemian
– Bohemian ♦, Commented Mar 3, 2017 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
– Strawberry, Commented Mar 3, 2017 at 19:15

Anand · Accepted Answer · 2017-03-03 19:04:45Z

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 3, 2017 at 19:04

answered Mar 3, 2017 at 18:56

Anand

1,12310 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

SqlZim Over a year ago

Nicely done, I missed the different asha earlier.

Anand Over a year ago

Thank you! @SqlZim, means a lot coming from a guru :)

lemur Over a year ago

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?

Anand Over a year ago

@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.

lemur Over a year ago

Thanks Anand! I understood the technique and applied it sucessfully to my needs.

|

Manohar Godase · Accepted Answer · 2017-03-03 19:00:19Z

-1

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

use above query to unique Id records.

answered Mar 3, 2017 at 19:00

Manohar Godase

1094 bronze badges

1 Comment

Strawberry Over a year ago

DISTINCT is not a function. And this query returns indeterminate results. Bad.

Collectives™ on Stack Overflow

Select first row for each different column value [duplicate]

2 Answers 2

6 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Linked

Related