Paul - 9 months ago 46

SQL Question

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`

.