MySQL GROUP BY maintaining sequential order of records

[emmad]

I’m working with MySQL and running into an issue with grouping data while preserving the original sequence.

Here’s my sample table called Activities:

activity_id | person_id | activity_name | sequence_num
--------------------------------------------------------
101         |    5      | Work1         |    1
102         |    5      | Work2         |    2  
103         |    7      | Work3         |    3
104         |    5      | Work4         |    4
105         |    5      | Work5         |    5

I want to get this result that shows person_id in the order they appear:

person_id
---------
    5
    7
    5

But when I use GROUP BY person_id, it doesn’t keep the original order and just gives me:

person_id
---------
    5
    7

Can this be done with a straightforward SQL query?

[SwimmingShark]

You’re confusing what GROUP BY does with what you actually want. GROUP BY collapses rows with identical values into single rows for aggregation - that’s why you’re losing the sequential duplicates. You don’t need grouping at all since you want to keep every occurrence of person_id in its original spot. Just use SELECT person_id FROM Activities ORDER BY sequence_num and you’ll get exactly what you’re after. I’ve seen this mix-up before - sometimes the simplest query without any grouping is the right answer.

[joec]

GROUP BY won’t maintain the order you need as it only aggregates unique values. Instead, you should query the table directly and order by sequence_num to retrieve all occurrences of person_id in their original sequence: SELECT person_id FROM Activities ORDER BY sequence_num;. This way, duplicates are preserved, and the desired order is achieved.

[Mike71]

yeah, group by can be a pain. maybe just use SELECT person_id FROM Activities ORDER BY sequence_num to keep the order and get all instances.