SQL query with specialized ordering of results

Question

I have a table that has 3 columns representing the stops in a bus route.

ID
stop_name
stop_order

I am wanting to return a list of stops starting with the current stop (which I know). So if the current stop is stop number 5 then what is returned will be as follows:

stop_order
5
6
7
8
1
2
3
4

I tried:

Select * from routes where stop_order >= 3 and route = 'Red' 
Union 
Select * from routes where stop_order < 3 and route = 'Red

and it works if the data was entered into the table in the order of the stops. If it wasn't then it returns the data in the order it was entered.

Answer 1

Using this would work:

(Select * from routes where stop_order >= 3 and route = 'Red' Order By ID)
Union 
(Select * from routes where stop_order < 3 and route = 'Red' Order By ID)

Edit: added forgotten parenthesis.

Answer 2

You can do this in one query to save on table accesses by using case statements.

select * from routes
where route = 'Red'
order by case when stop_order >= 3 then 0 else 1 end, stop_order
;

Corrected!

Answer 3

Try Order by stop_order to both the sql statements. By default the order by will order the results in ascending order

Answer 4

I think Patrick almost had it:

(SELECT * FROM routes WHERE stop_order >= 5 ORDER BY stop_order)
UNION ALL
(SELECT * FROM routes WHERE stop_order < 5 ORDER BY stop_order)