CODEWITHSUNDEEP
sqlmysqlsql-order-by
Brian
Brian

Reputation: 61

SQL query with specialized ordering of results

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.

Upvotes: 0

Views: 214

Answers (5)

Sourcery
Sourcery

Reputation: 656

a'r answer is correct for the question asked. However I want to further optimize the code and also make it possible to use DISTINCT in the query which isnt possible with the solution given.

After looking around a bit more I found out you can move the CASE WHEN statement into the select instead of in the order as coded below:

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

Hope this helps a few others having problems with ordering and also using DISTINCT.

Upvotes: 0

Patrick Karcher
Patrick Karcher

Reputation: 23603

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.

Upvotes: 0

a&#39;r
a&#39;r

Reputation: 36999

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!

Upvotes: 6

Marcus Adams
Marcus Adams

Reputation: 53830

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)

Upvotes: 0

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55489

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

Upvotes: 0

Related Questions