StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to do this GROUP BY with the wanted result?
text
Body
copied!<p>Basically, I have a table with all the bus stops of a route with the time_from_start value, that helps to put them in a good order.</p> <pre><code>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`) ) </code></pre> <p>I want to return for each stop of a line the time to go to the next stop.</p> <p>I tried with this QUERY :</p> <pre><code>SELECT r1.station_id, r2.station_id, r1.route_id, COUNT(*), (r2.time_from_start - r1.time_from_start) as time FROM api_routestop r1 LEFT JOIN api_routestop r2 ON r1.route_id = r2.route_id AND r1.id <> r2.id GROUP BY r1.station_id HAVING time >= 0 ORDER BY r1.route_id, r1.time_from_start, r2.time_from_start </code></pre> <p>But the group by seams not to work and the result looks like :</p> <pre><code>+------------+------------+----------+----------+------+ | 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 | +------------+------------+----------+----------+------+ </code></pre> <p>If I do that : </p> <pre><code>SELECT r1.station_id, r2.station_id, r1.route_id, COUNT(*), (r2.time_from_start - r1.time_from_start) as time FROM api_routestop r1 LEFT JOIN api_routestop r2 ON r1.route_id = r2.route_id AND r1.id <> r2.id GROUP BY r1.station_id, r2.station_id, r1.route_id HAVING time >= 0 ORDER BY r1.route_id, r1.time_from_start, r2.time_from_start </code></pre> <p>I am approching :</p> <pre><code>+------------+------------+----------+----------+------+ | 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) </code></pre> <p>But what do I have to do to have only the first result for the same r1.station_id and r1.route_id ?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload