mysql, using group_concat

Could anyone let me know how to limit the num开发者_开发技巧ber of values in GROUP_CONCAT for each group in MySQL? I am using the below query which also produces more than 2 concatenated values for each group

  SELECT GROUP_CONCAT(remaining) 
    FROM `busroute` 
GROUP BY bus

Could anyone let me know how to modify the above query for my problem?

I don't know of a way to limit the number of rows that are grouped, and I don't think that you can do it.

But if you are only going to want to have two rows that you want to group, you can do the grouping and group_concat manually and only group two rows at a time:

SELECT t1.bus, concat(t1.remaining, ',', t2.remaining) 
FROM busroute as t1 
    JOIN busroute as t2 on t1.bus = t2.bus 
WHERE t1.id < t2.id

Here we've just gotten two copies of the busroute table and then joined them together on the bus number, then we take some unique column value in the row (which could be any column as long as it's column as the unique attribute set on it) and eliminate matches of a row against its self. I used '<' rather than '<>' since I only want to match the same pair of non-unique rows once.