J
Here is the query you need:
set @groupnum = 0;
set @groupon = 0;
set @inc = 1;
select grpn,GROUP_CONCAT(diary ORDER BY id) itinerary_step from
(select
@inc := IF(@groupon=0,1,0) inc,
@groupnum := @groupnum + @inc grpn,
@s_tag := IF(diary='start',1,0) stag,
@e_tag := IF(diary='end', 1,0) etag,
@groupon := IF(diary='start',1,@groupon) g1,
@groupon := IF(diary='end', 0,@groupon) g2,
(@ordernum:=@e_tag*1000000+@s_tag) ord,
id,diary from diary
) A WHERE ord NOT IN (1,1000000) GROUP BY grpn;
First, here is your sample data
mysql> use all_db
Database changed
mysql> drop table if exists diary;
Query OK, 0 rows affected (0.03 sec)
mysql> create table diary
-> (
-> id int not null auto_increment,
-> diary varchar(25),
-> primary key (id)
-> );
Query OK, 0 rows affected (0.11 sec)
mysql> insert into diary (diary) values ('breakfast'),('walk'),('start'),
-> ('office works'),('office projects'),('end'),('taxi'),('start'),
-> ('preparing for meeting'),('doing the meeting'),('end'),('night');
Query OK, 12 rows affected (0.06 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> select * from diary;
+----+-----------------------+
| id | diary |
+----+-----------------------+
| 1 | breakfast |
| 2 | walk |
| 3 | start |
| 4 | office works |
| 5 | office projects |
| 6 | end |
| 7 | taxi |
| 8 | start |
| 9 | preparing for meeting |
| 10 | doing the meeting |
| 11 | end |
| 12 | night |
+----+-----------------------+
12 rows in set (0.00 sec)
mysql>
Using iterative variable manipulation
set @groupnum = 0;
set @groupon = 0;
set @inc = 1;
select * from
(select
@inc := IF(@groupon=0,1,0) inc,
@groupnum := @groupnum + @inc grpn,
@s_tag := IF(diary='start',1,0) stag,
@e_tag := IF(diary='end', 1,0) etag,
@groupon := IF(diary='start',1,@groupon) g1,
@groupon := IF(diary='end', 0,@groupon) g2,
(@ordernum:=@e_tag*1000000+@s_tag) ord,
id,diary from diary
) A;
Here is what it generates:
mysql> set @groupnum = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> set @groupon = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> set @inc = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from
-> (select
-> @inc := IF(@groupon=0,1,0) inc,
-> @groupnum := @groupnum + @inc grpn,
-> @s_tag := IF(diary='start',1,0) stag,
-> @e_tag := IF(diary='end', 1,0) etag,
-> @groupon := IF(diary='start',1,@groupon) g1,
-> @groupon := IF(diary='end', 0,@groupon) g2,
-> (@ordernum:=@e_tag*1000000+@s_tag) ord,
-> id,diary from diary
-> ) A;
+-----+------+------+------+------+------+---------+----+-----------------------+
| inc | grpn | stag | etag | g1 | g2 | ord | id | diary |
+-----+------+------+------+------+------+---------+----+-----------------------+
| 1 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | breakfast |
| 1 | 2 | 0 | 0 | 0 | 0 | 0 | 2 | walk |
| 1 | 3 | 1 | 0 | 1 | 1 | 1 | 3 | start |
| 0 | 3 | 0 | 0 | 1 | 1 | 0 | 4 | office works |
| 0 | 3 | 0 | 0 | 1 | 1 | 0 | 5 | office projects |
| 0 | 3 | 0 | 1 | 1 | 0 | 1000000 | 6 | end |
| 1 | 4 | 0 | 0 | 0 | 0 | 0 | 7 | taxi |
| 1 | 5 | 1 | 0 | 1 | 1 | 1 | 8 | start |
| 0 | 5 | 0 | 0 | 1 | 1 | 0 | 9 | preparing for meeting |
| 0 | 5 | 0 | 0 | 1 | 1 | 0 | 10 | doing the meeting |
| 0 | 5 | 0 | 1 | 1 | 0 | 1000000 | 11 | end |
| 1 | 6 | 0 | 0 | 0 | 0 | 0 | 12 | night |
+-----+------+------+------+------+------+---------+----+-----------------------+
12 rows in set (0.00 sec)
mysql>
With a few refinements
ignoring ord=1 and ord=1000000
GROUP BY on grpn
Here is the final query first mentioned
set @groupnum = 0;
set @groupon = 0;
set @inc = 1;
select grpn,GROUP_CONCAT(diary ORDER BY id) itinerary_step from
(select
@inc := IF(@groupon=0,1,0) inc,
@groupnum := @groupnum + @inc grpn,
@s_tag := IF(diary='start',1,0) stag,
@e_tag := IF(diary='end', 1,0) etag,
@groupon := IF(diary='start',1,@groupon) g1,
@groupon := IF(diary='end', 0,@groupon) g2,
(@ordernum:=@e_tag*1000000+@s_tag) ord,
id,diary from diary
) A WHERE ord NOT IN (1,1000000) GROUP BY grpn;
Here it is executed:
mysql> set @groupnum = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> set @groupon = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> set @inc = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> select grpn,GROUP_CONCAT(diary ORDER BY id) itinerary_step from
-> (select
-> @inc := IF(@groupon=0,1,0) inc,
-> @groupnum := @groupnum + @inc grpn,
-> @s_tag := IF(diary='start',1,0) stag,
-> @e_tag := IF(diary='end', 1,0) etag,
-> @groupon := IF(diary='start',1,@groupon) g1,
-> @groupon := IF(diary='end', 0,@groupon) g2,
-> (@ordernum:=@e_tag*1000000+@s_tag) ord,
-> id,diary from diary
-> ) A WHERE ord NOT IN (1,1000000) GROUP BY grpn;
+------+-----------------------------------------+
| grpn | itinerary_step |
+------+-----------------------------------------+
| 1 | breakfast |
| 2 | walk |
| 3 | office works,office projects |
| 4 | taxi |
| 5 | preparing for meeting,doing the meeting |
| 6 | night |
+------+-----------------------------------------+
6 rows in set (0.00 sec)
mysql>
Give it a Try !!!
CAVEAT
I tried out @a1ex07's query against my sample data
mysql> select d.id1,
-> CASE
-> WHEN COUNT(*) >1 THEN 'concatenated'
-> ELSE NULL
-> END AS `type`,
-> GROUP_CONCAT(
-> case
-> when diary = 'start' or diary = 'end' then null
-> else diary
-> end
-> order by id
-> ) as diary
-> FROM
-> (
-> SELECT x.id, x.diary,
-> CASE WHEN start_id IS NULL OR end_id IS NULL OR start_id > end_id THEN id
-> ELSE start_id
-> END as id1,
-> end_id
-> FROM
-> (
-> select a.*,
-> (select max(id) from diary b where b.diary ='start' and b.id (select min(id) from diary b where b.diary ='end' and b.id >=a.id) as end_id
->
-> from diary a
-> )x
-> )d
-> group by id1,end_id;
+------+--------------+-----------------------------------------+
| id1 | type | diary |
+------+--------------+-----------------------------------------+
| 1 | NULL | breakfast |
| 2 | NULL | walk |
| 3 | concatenated | office works,office projects |
| 3 | NULL | taxi |
| 8 | concatenated | preparing for meeting,doing the meeting |
| 12 | NULL | night |
+------+--------------+-----------------------------------------+
6 rows in set (0.00 sec)
mysql>
By George, it works !!! He get's +1 for getting his answer in first.