MySQL grouping while preserving sequential order

[SparklingGem]

I’m working with MySQL and running into an issue with my query results.

I have a database table called Activities with this structure:

activity_id | person_id | activity_name | sequence_num
-------------------------------------------------------
100         | 5         | Work1         | 10
101         | 5         | Work2         | 20
102         | 8         | Work3         | 30
103         | 5         | Work4         | 40
104         | 5         | Work5         | 50

What I want to get back is:

person_id
---------
5
8
5

But when I use GROUP BY person_id, it combines all the rows and I only get:

person_id
---------
5
8

I need to maintain the original sequence where person_id 5 appears, then 8, then 5 again based on the order they appear in the data. Can this be done with a straightforward SQL query?

[RunningRiver]

I’ve tackled this type of sequential grouping before - there’s a cleaner way using variables. You assign group numbers whenever person_id changes from the previous row. Here’s the query: SELECT person_id FROM (SELECT person_id, @group_num := CASE WHEN @prev_person != person_id THEN @group_num + 1 ELSE @group_num END as grp, @prev_person := person_id FROM Activities CROSS JOIN (SELECT @group_num := 0, @prev_person := ‘’) r ORDER BY sequence_num) grouped GROUP BY grp ORDER BY MIN(sequence_num). It creates invisible group numbers that bump up each time person_id switches, then groups by those numbers while keeping the order. Variables are usually more readable than window functions for this case, though both work fine.

[Tom01_Wonder]

GROUP BY won’t work here. You need to find consecutive sequences of the same person_id values. I ran into this same issue last year with event logs. ROW_NUMBER() with a window function saved me - it detects when person_id changes from the previous row. Here’s what worked: SELECT person_id FROM ( SELECT person_id, LAG(person_id) OVER (ORDER BY sequence_num) as prev_person, ROW_NUMBER() OVER (ORDER BY sequence_num) as rn FROM Activities ORDER BY sequence_num ) t WHERE rn = 1 OR person_id != prev_person; This compares each row’s person_id with the previous one. When there’s a change (or it’s the first row), that person_id gets included. LAG is the key - it keeps your data sequential while catching transitions between different person IDs.