CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
kayahr
kayahr

Reputation: 22010

Select only newest grouped entries

I have a table with data like this:

+-----------+-------+------+----------+
| timestamp | event | data | moreData |
+-----------+-------+------+----------+
| 100000000 |     1 |   10 |       20 |
| 100000001 |     1 |   15 |       10 |
| 100000002 |     1 |   30 |       30 |
| 100000003 |     1 |    5 |       50 |
| 100000004 |     2 |  110 |      120 |
| 100000005 |     2 |  115 |      110 |
| 100000006 |     2 |  130 |      130 |
| 100000007 |     2 |   15 |      150 |
+-----------+-------+------+----------+

Now I want to select only the newest rows for each event. So in the end I want to have this result set:

+-----------+-------+------+----------+
| timestamp | event | data | moreData |
+-----------+-------+------+----------+
| 100000003 |     1 |    5 |       50 |
| 100000007 |     2 |   15 |      150 |
+-----------+-------+------+----------+

So far I was not able to do this. In MySQL I can use "GROUP BY event" but then I get some random row from the database, not the newest. ORDER BY doesn't help because the grouping is done before ordering. Using an aggregation like MAX(timestamp) while grouping by event also doesn't help because then the timestamp is the newest but "data" and "moreData" is still from some other random row.

I guess I have to do a sub select so I have to first get the latest timestamp like this:

SELECT MAX(timestamp), event FROM mytable GROUP BY event

and then use the result set to filter a second SELECT, but how? And maybe there is a clever way to do it without a sub select?

Upvotes: 1

Views: 230

Answers (4)

Ben Mosher
Ben Mosher

Reputation: 13381

AFAIK, sub select is your best option, as follows:

SELECT * 
FROM mytable mt 
    JOIN ( SELECT MAX(timestamp) as max, event 
           FROM mytable 
           GROUP BY event) m_mt 
    ON (mt.timestamp = m_mt.max AND mt.event = m_mt.event);

Upvotes: 2

Richard H
Richard H

Reputation: 39055

SELECT * FROM 
(SELECT * FROM mytable ORDER BY timestamp DESC) AS T1 
GROUP BY event;

Upvotes: 1

Arnaud Le Blanc
Arnaud Le Blanc

Reputation: 99889

SELECT   e2.*
FROM     events e
JOIN     events e2 ON e2.event = e.event AND e2.timestamp = MAX(e2.timestamp)
GROUP BY e.id

Upvotes: 0

Andomar
Andomar

Reputation: 238048

You could use an inner join as a filter:

select  *
from    events e1
join    (
        select  event
        ,       max(timestamp) as maxtimestamp
        from    events
        group by
                event
        ) e2
on      e1.event = e2.event
        and e1.tiemstamp = e2.maxtimestamp

Upvotes: 2

Related Questions