#SQLite
サブクエリを使う。[[SQLite]]以外のデータベースでは専用の構文が用意されていることもある。
```sql
SELECT
service_route_id,
service_route_direction_id,
route_id,
route_short_name,
route_long_name,
GROUP_CONCAT(stop_name, ',') AS stop_names,
GROUP_CONCAT(stop_id, ',') AS stop_ids
FROM
-- サブクエリでORDER_BYしておく
(
SELECT
sr.service_route_id AS service_route_id,
sr.direction_id AS service_route_direction_id,
r.route_id AS route_id,
r.route_short_name AS route_short_name,
r.route_long_name AS route_long_name,
st.stop_id AS stop_id,
st.stop_name AS stop_name
FROM
stop_times stt
INNER JOIN trips t
ON stt.trip_id == t.trip_id
INNER JOIN trips2service_routes t2sr
ON stt.trip_id = t2sr.trip_id
INNER JOIN service_routes sr
ON t2sr.service_route_id = sr.service_route_id
INNER JOIN stops st
ON stt.stop_id == st.stop_id
INNER JOIN routes r
ON t.route_id == r.route_id
ORDER BY stt.trip_id, stt.stop_sequence
)
GROUP BY
service_route_id, service_route_direction_id
ORDER BY
service_route_id, service_route_direction_id
```