How to GROUP BY same value in different rows with condition? (MYSQL)

user_5525703 0
Viewed 131

I have this table:

+-----------+-----------+---------------------+
| id        | member_id | date                |
+-----------+-----------+---------------------+
| 1         |     2     | 2020-07-27 21:53:46 |
| 2         |     1     | 2020-07-27 22:03:58 |
| 3         |     1     | 2020-07-27 22:09:16 |
| 4         |     1     | 2020-07-27 22:11:33 |
| 5         |     2     | 2020-07-27 22:12:21 |
-----------------------------------------------

And I would like to GROUP BY something like this:

+-----------+-----------+---------------------+
| id        | member_id | date                |
+-----------+-----------+---------------------+
| 1         |     2     | 2020-07-27 21:53:46 |
| 2         |     1     | 2020-07-27 22:03:58 |
| 5         |     2     | 2020-07-27 22:12:21 |
-----------------------------------------------

But when I put GROUP BY member_id it just return me two rows (1, 2)

gaps-and-islands window-functions group-by mysql sql
1 Answers

I understand this as a gaps-and-island problem, where you want to display the rows whose member_id is different than the "previous" row.

Here is an approach using window functions (available in MySQL 8.0): lag() gives the member_id on the previous row, that we can then compare to the value on the current row:

select id, member_id, date
from (
    select t.*, lag(member_id) over(order by date) lag_member_id
    from mytable t
) t
where not lag_member_id <=> member_id 
order by date

Demo on DB Fiddlde:

id | member_id | date               
-: | --------: | :------------------
 1 |         2 | 2020-07-27 21:53:46
 2 |         1 | 2020-07-27 22:03:58
 5 |         2 | 2020-07-27 22:12:21
user_106767160
Related