CODEWITHSUNDEEP
mysql
markzzz
markzzz

Reputation: 47947

How to return the most recent value with group by

I have this query :

SELECT history.trackid, tracklist.artist, tracklist.event, tracklist.data, history.date
FROM history JOIN tracklist ON history.trackid=tracklist.trackid 
GROUP BY trackid 
ORDER BY history.date DESC

but I'd like to return the most recent trackid (by date) when I group on history.

Tried with that GROUP BY and ORDER BY DESC, but is not the most recent. Where am I wrong?

Upvotes: 3

Views: 1810

Answers (2)

Nico Huysamen
Nico Huysamen

Reputation: 10417

The reason it is not sorting by most recent, is because GROUP BY creates an aggregate result. You could try:

SELECT   h.trackid, t.artist, t.event, t.data
FROM     history AS h
  JOIN   tracklist AS t
    ON   h.trackid = t.trackid 
GROUP BY h.trackid 
ORDER BY MAX(h.date) DESC

Because you are already aggregating results, the MAX(history.date) should return the maximum (or most recent) date for each group.

Upvotes: 4

Quassnoi
Quassnoi

Reputation: 425251

Assuming id is a PRIMARY KEY on history:

SELECT  t.trackid, t.artist, t.event, t.data, h.date
FROM    tracklist t
LEFT JOIN
        history h
ON      h.id =
        (
        SELECT  hi.id
        FROM    history
        WHERE   hi.trackid = t.trackid
        ORDER BY
                hi.trackid DESC, hi.date DESC, hi.id DESC
        LIMIT 1
        )

Create an index on history (trackid, date, id) for this to work fast.

Upvotes: 4

Related Questions