MySQL Select grouping and with latest timestamp

Question

I have a table like this:

+----+----+---------------------+---------------+
|lid |uid |timestamp            |token          |
|----+----+---------------------+---------------+
| 10 | 10 | 2014-02-03 12:17:18 | 52efcf118a0ba |
| 45 | 10 | 2014-02-03 12:51:13 | 52efd7046022d |
| 20 | 10 | 2014-02-04 03:47:59 | 52f0a931d3945 |
| 12 | 11 | 2014-02-04 05:21:57 | 52f0bf389371b |
| 34 | 11 | 2014-02-05 06:36:26 | 52f222384fe2d |
| 12 | 11 | 2014-02-05 07:06:42 | 52f229500b403 |
| 32 | 12 | 2014-02-04 05:21:57 | 52f0bf389371b |
| 55 | 12 | 2014-02-05 06:36:26 | 52f222384fe2d |
| 61 | 12 | 2014-02-05 07:06:42 | 52f229500b403 |

lid and uid are foreign keys and the table does not have a unique column.

I want to select rows grouping the uid BUT with the latest timestamp. For example, from the above data i only want these rows:

+----+----+---------------------+---------------+
|lid |uid |timestamp            |token          |
|----+----+---------------------+---------------+
| 20 | 10 | 2014-02-04 03:47:59 | 52f0a931d3945 |
| 12 | 11 | 2014-02-05 07:06:42 | 52f229500b403 |
| 61 | 12 | 2014-02-05 07:06:42 | 52f229500b403 |

possible duplicate of [Get top n records for each group of grouped results](http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) — Marc B, Feb 05 '14 at 18:54

score 1 · Answer 1 · answered Feb 05 '14 at 18:55

Here is a typical way in MySQL:

select t.*
from table t
where not exists (select 1
                  from table t2
                  where t2.uid = t.uid and
                        t2.timestamp > t.timestamp
                 );

The logic is: get me all rows from the table, where there is no timestamp for the same uid larger than this timestamp. You should have an index on table(uid, timestamp) for performance.

MySQL Select grouping and with latest timestamp

1 Answers1