sql - How to do this GROUP BY with the wanted result? -

basically, have table bus stops of route time_from_start value, helps put them in order.

create table `api_routestop` (   `id` int(11) not null auto_increment,   `route_id` int(11) not null,   `station_id` varchar(10) not null,   `time_from_start` int(11) not null,   primary key (`id`),   key `api_routestop_4fe3422a` (`route_id`),   key `api_routestop_15e3331d` (`station_id`) ) 

i want return each stop of line time go next stop.

i tried query :

select r1.station_id, r2.station_id, r1.route_id, count(*), (r2.time_from_start - r1.time_from_start) time api_routestop r1 left join api_routestop r2 on r1.route_id = r2.route_id , r1.id <> r2.id group r1.station_id having time >= 0 order r1.route_id, r1.time_from_start, r2.time_from_start 

but group seams not work , result looks :

+------------+------------+----------+----------+------+ | station_id | station_id | route_id | count(*) | time | +------------+------------+----------+----------+------+ | rub01      | sal01      |        1 |       16 |    1 | | lyc02      | sch02      |        2 |       17 |    2 | | paq01      | pob01      |        3 |       15 |    1 | | lat02      | gco02      |        4 |       16 |    1 | | sup01      | tur01      |        5 |      132 |    1 | | oeu02      | ctc02      |        6 |       20 |    2 | | ver02      | elo02      |        7 |       38 |    1 | | can01      | mbo01      |        8 |       70 |    1 | | ver01      | elo01      |        9 |       77 |    1 | | mch01      | for02      |       10 |       77 |    1 | +------------+------------+----------+----------+------+ 

if :

select r1.station_id, r2.station_id, r1.route_id, count(*), (r2.time_from_start - r1.time_from_start) time api_routestop r1 left join api_routestop r2 on r1.route_id = r2.route_id , r1.id <> r2.id group r1.station_id, r2.station_id, r1.route_id having time >= 0 order r1.route_id, r1.time_from_start, r2.time_from_start 

i approching :

+------------+------------+----------+----------+------+ | station_id | station_id | route_id | count(*) | time | +------------+------------+----------+----------+------+ | rub01      | sal01      |        1 |        1 |    1 | | rub01      | arm01      |        1 |        1 |    2 | | rub01      | mav01      |        1 |        1 |    4 | | rub01      | col01      |        1 |        1 |    5 | | rub01      | str01      |        1 |        1 |    6 | | rub01      | jau01      |        1 |        1 |    7 | | rub01      | cdp01      |        1 |        1 |    9 | | rub01      | rep01      |        1 |        1 |   11 | | rub01      | cot01      |        1 |        1 |   12 | | rub01      | ctr01      |        1 |        1 |   14 | | rub01      | fly01      |        1 |        1 |   15 | | rub01      | lib01      |        1 |        1 |   17 | | rub01      | bru01      |        1 |        1 |   18 | | rub01      | sch01      |        1 |        1 |   20 | | rub01      | lyc01      |        1 |        1 |   22 | | rub01      | res01      |        1 |        1 |   24 | | sal01      | arm01      |        1 |        1 |    1 | | sal01      | mav01      |        1 |        1 |    3 | | sal01      | col01      |        1 |        1 |    4 | | sal01      | str01      |        1 |        1 |    5 | | sal01      | jau01      |        1 |        1 |    6 | | sal01      | cdp01      |        1 |        1 |    8 | | sal01      | rep01      |        1 |        1 |   10 | | sal01      | cot01      |        1 |        1 |   11 | | sal01      | ctr01      |        1 |        1 |   13 | | sal01      | fly01      |        1 |        1 |   14 | | sal01      | lib01      |        1 |        1 |   16 | | sal01      | bru01      |        1 |        1 |   17 | | sal01      | sch01      |        1 |        1 |   19 | | sal01      | lyc01      |        1 |        1 |   21 | ... 3769 rows in set (0.07 sec) 

but have have first result same r1.station_id , r1.route_id ?

you're getting lot of results because getting every stop joined every other stop on same route.

so you'll need identify "next" stop stop has same route id has minimum time start later current one

update added routeid next_stop sub query deal case of stations used in multiple routes

select     r1.station_id,      r2.station_id,      r1.route_id,      r2.time_from_start - r1.time_from_start time  api_routestop r1  inner join (select           r1.station_id , r2.route_id, min(r2.time_from_start) next_time_from_start    api_routestop r1   left join api_routestop r2 on r1.route_id = r2.route_id , r1.id <> r2.id        , r2.time_from_start > r1.time_from_start   group r1.station_id, r2.route_id) next_stop  on r1.station_id = next_stop.station_id    , r1.route_id = next_stop.route_id left join api_routestop r2  on r2.time_from_start = r2.next_time_from_start     , r1.route_id = r2.route_id , r2.time_from_start > r1.time_from_start