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 sir       |
| 147 | 11222 |   283 |  1123 | 2012-08-21 03:29:55 | yo           |
| 140 | 11222 | 11223 |  1121 | 2012-08-21 02:12:13 | hello!       |
| 139 | 11222 |   249 |  1120 | 2012-08-21 02:11:53 | hi :)        |
| 140 | 11222 | 11223 |  1119 | 2012-08-21 02:11:26 | hi :)        |
| 140 | 11222 | 11223 |  1118 | 2012-08-21 02:11:08 | hi too       |
| 139 | 11222 |   249 |  1117 | 2012-08-21 02:11:00 | :P           |
| 139 | 11222 |   249 |  1116 | 2012-08-21 02:10:57 | hi           |
| 139 | 11222 |   249 |  1115 | 2012-08-21 02:10:51 | helo         |
| 139 | 11222 |   249 |  1114 | 2012-08-21 02:06:19 | hi           |
| 139 | 11222 |   249 |  1113 | 2012-08-21 02:05:45 | hi baby      |
| 139 | 11222 |   249 |  1112 | 2012-08-21 02:05:00 | hi           |
| 139 | 11222 |   249 |  1111 | 2012-08-21 02:04:41 | hi           |
| 140 | 11222 | 11223 |  1110 | 2012-08-21 02:04:26 | hi           |
| 140 | 11222 | 11223 |  1108 | 2012-08-21 01:47:40 | hey :)       |
| 139 | 11222 |   249 |  1107 | 2012-08-21 01:44:43 | hi           |
| 138 | 11222 |   241 |  1106 | 2012-08-21 01:44:11 | hi           |
| 138 | 11222 |   241 |  1105 | 2012-08-21 01:09:20 | conv 1 msg 1 |
+-----+-------+-------+-------+---------------------+--------------+

How to extract only the first occurrence of each cid? The resulting table would be:

+-----+-------+-------+-------+---------------------+--------------+
| 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        |
| 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            |
| 147 | 11222 |   283 |  1123 | 2012-08-21 03:29:55 | yo           |
| 140 | 11222 | 11223 |  1121 | 2012-08-21 02:12:13 | hello!       |
| 139 | 11222 |   249 |  1120 | 2012-08-21 02:11:53 | hi :)        |
+-----+-------+-------+-------+---------------------+--------------+

Bohemian · Accepted Answer · 2012-11-04T06:13:51.313

90

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

edited Nov 04 '12 at 06:13

answered Aug 22 '12 at 03:28

Bohemian

412,405
93
575
722

7

I needed to add `ORDER by time DESC` to get it sorted with most recent at the top, but otherwise this looks to be working as expected. Sheesh, that was quick. Thanks! – TPoy Aug 22 '12 at 03:38
@Bohemian, can you tell me what means the **'x'** before the _GROUP BY_? – Carlos Durán May 25 '16 at 09:54
1

@carlos all subqueries must be given an alias, even if you don't refer to it. It's a requirement of SQL syntax. I usually use "x" as the alas if I'm not going to actually use it. You could change "x" to just about anything you want and the query will still work, but you can't leave it out altogether – Bohemian May 25 '16 at 12:31
5

Is this answer still correct? I just tried this, and got `Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database.table.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by` – felwithe Jul 19 '18 at 03:15
@felwithe In more recent releases, sql_mode=only_full_group_by is the default (it was previously not enabled by default), which is causing this query to produce that erro. You can disable `only_full_group_by` - see https://stackoverflow.com/questions/23921117/disable-only-full-group-by – Bohemian Jul 19 '18 at 04:23
1

For some reason using the second example to sort in desc order doesn't work for me. I still get results in ASC order (using MySQL version 8) – Andrew Jan 27 '22 at 21:03
@Andrew do you get the rows you want (ie the earliest rows) but in the wrong order, or do you get the wrong rows (ie the latest rows)? – Bohemian Jan 27 '22 at 21:12
1

@Bohemian I got the same results as if I was running the first query. Not sure if it has something to do with the table, but the table logs user activity with a timestamp. I want to group by the userid (foreign key) showing their last transaction date. However it shows the grouped users by their first transaction date, even when attempting to sort it. For now I got around it by using John Woo's method using MAX(transaction_date) for now – Andrew Jan 28 '22 at 22:37
@Andrew I have the same problem, did you figure out why it is not sorting by any chance? – temirbek Sep 01 '22 at 09:20
1

@temirbek sorry it's been awhile but I believe I stuck with John Woo's example using MAX() or MIN(). – Andrew Sep 01 '22 at 16:37

score 11 · Answer 2 · edited Jul 06 '20 at 15:35

11

Try this one,

SELECT *
FROM tableName a 
INNER JOIN (
    SELECT cid, MIN(`time`) AS MinTime
    FROM tableName
    GROUP BY cid
) b 
ON a.CID = B.cid AND a.time = b.MinTime

edited Jul 06 '20 at 15:35

Yuriy Petrovskiy

7,888
10
30
34

answered Aug 22 '12 at 03:28

John Woo

258,903
69
498
492

score 8 · Answer 3 · answered Sep 19 '18 at 19:51

8

In MySQL 8, you would use window functions for this

SELECT cid, pid, rid, clink, time, snippet
FROM (
  SELECT t.*, ROW_NUMBER() OVER (PARTITION BY cid ORDER BY time) rn
  FROM t
) t
WHERE rn = 1

answered Sep 19 '18 at 19:51

Lukas Eder

211,314
129
689
1,509

score 1 · Answer 4 · answered Aug 22 '12 at 03:27

You could use a filtering join:

select  *
from    (
        select  cid
        ,       min(time) as min_time
        from    YourTable
        group by
                cid
        ) filter
join    YourTable yt
on      filter.cid = yt.cid
        and filter.min_time = yt.time

score 1 · Answer 5 · edited Aug 23 '20 at 10:12

1

I know it's an old thread, the accepted solution would only retrieve me the columns that had more than one occurrence.

This worked for me:

SELECT cid, pid, rid, clink, MAX(time), snippet 
FROM mytable 
GROUP BY cid

edited Aug 23 '20 at 10:12

marc_s

732,580
175
1,330
1,459

answered May 31 '17 at 09:10

Carolina Marques

19
2

MySQL Select rows on first occurrence of each unique value

5 Answers5

Linked