How to group concat from MySQL and not repeat the same value if next value is same as current value

Question

I have a table (location) like this

id | loc id
1  | Location A
2  | Location B
3  | Location B
4  | Location C
5  | Location A
6  | Location B
7  | Location B

i want my result from MySQL to be like this

Location A, Location B, Location C, Location A, Location B

but this is what i get instead

Location A, Location B, Location B, Location C, Location A, Location B, Location

how to achieve this?

Answer 1

For MySql 8.0+ you can use window function LAG() to filter out rows that have the same locid as the previous row:

select group_concat(t.locid order by t.id) locations 
from (
  select *, lag(locid) over (order by id) prev
  from location
) t  
where t.locid <> t.prev or t.prev is null

For previous versions and if there are not gaps between the ids you can use NOT EXISTS:

select group_concat(locid order by id) locations 
from location l
where not exists (select 1 from location where locid = l.locid and id = l.id - 1)

If there are gaps between the ids then use a correlated subquery in the WHERE clause to get the value of locid of the previous id:

select group_concat(locid order by id) locations 
from location l
where l.locid <> coalesce(
  (select locid from location where id < l.id order by id desc limit 1), 
  ''
)

See the demo.
Results:

> | locations                                              |
> | :----------------------------------------------------- |
> | Location A,Location B,Location C,Location A,Location B |