MySQL Select rows on first occurrence of each unique value

Question

Let's say you have the following table (the column of interest here is `cid`): ``` +-----+-------+-------+-------+---------------------+--------------+ | cid | pid | rid | clink | time | snippet | +-----+-------+-------+-------+---------------------+--------------+ | 155 | 11222 | 1499 | 1137 | 2012-08-22 03:05:06 | hi | | 138 | 11222 | 241 | 1136 | 2012-08-21 05:25:00 | again | | 138 | 11222 | 241 | 1135 | 2012-08-21 05:16:40 | hi | | 155 | 11222 | 1499 | 1134 | 2012-08-21 05:11:00 | hi cute | | 140 | 11222 | 11223 | 1133 | 2012-08-21 05:05:18 | hi | | 154 | 11222 | 565 | 1132 | 2012-08-21 05:04:47 | 7 | | 153 | 11222 | 272 | 1131 | 2012-08-21 05:04:41 | 6 | | 146 | 11222 | 362 | 1130 | 2012-08-21 05:04:33 | 5 | | 152 | 11222 | 364 | 1129 | 2012-08-21 05:04:27 | 4 | | 151 | 11222 | 390 | 1128 | 2012-08-21 05:04:22 | 3 | | 150 | 11222 | 333 | 1127 | 2012-08-21 05:04:16 | 2 | | 148 | 11222 | 268 | 1126 | 2012-08-21 05:04:10 | 1 | | 140 | 11222 | 11223 | 1125 | 2012-08-21 04:59:57 | hi…

Accepted Answer

mysql has a "cheat" for this:

```
select *
from mytable
group by cid;
```

That's all you need, because in mysql it allows you to not aggregate the non-grouped-by columns (other databases would throw a syntax error), in which case it outputs only the first occurrence of each group-by value(s). Note though that this won't guarantee the way in which the "first" occurrence is determined (it will be just how the rows are read in)

If you want a particular first occurrence, sort first, then apply the group-by cheat:

```
select *
from (
    -- order by the "time" column descending to get the "most recent" row
    select * from mytable order by time desc
    ) x
group by cid
```

MySQL Select rows on first occurrence of each unique value

Solution

Problem

Related problems