Paul Paul - 3 months ago 15
SQL Question

Get correct SQL order by

I have a query that selects trips by cities, and want to order by first those in City 1, then those in City 4. Many trips exist in 2 cities, so the database has 2 fields, StartCity & StartCity2. This SQL produces the wrong order:-

SELECT * FROM qryZZ WHERE (StartCity = 1 OR StartCity2 = 1 OR StartCity = 4
OR StartCity2 = 4) Order By Case StartCity When 1 Then 1 When 4 Then 2 End


The results currently are:-

Trip |StartCity|StartCity2
Trip1 | 3 | 4
Trip2 | 3 | 4
Trip3 | 1 | NULL
Trip4 | 1 | NULL
Trip5 | 4 | NULL
Trip6 | 4 | 3


What I want is first those trips with StartCity or StartCity2 = 1, then those with StartCity or StartCity2 = 4. Is this at all possible?

Answer

You need to include both columns in the order by logic:

select *
from qryZZ
where StartCity in (1, 4) or StartCity2 in (1, 4)
order by (case when StartCity = 1 or StartCity2 = 1 then 1
               else 2
          end);

You might also want to separate out the two cities:

order by (case when StartCity = 1 then 1
               when StartCity2 = 1 then 2
               when StartCity = 4 then 3
               when StartCity2 = 4 then 4
               else 10  -- should never happen but put them at the end anyway
          end);

The strangeness in your results is because NULL values appear first in the order by. Without a where clause, the sort value for "3" is NULL.