plbata plbata - 3 months ago 12
SQL Question

MS SQL ORDER BY ASC then DESC for addresses

I would like to order results up the street then back down the other side for field review workflow (100, 102, 104, 105, 103, 101).

I have :

ORDER BY
s.situs_street,
CAST([situs_num]%2 as integer),
CAST(situs_num as integer)


This gets me all the evens then the odds but they are both ascending.

Using :

ORDER BY
s.situs_street,
CAST([situs_num]%2 as integer) ASC,
CAST(situs_num as integer) DESC


puts both even/odd descending. Have failed using
CASE
statement as well since I'm reverse sorting the same field.

Answer
ORDER BY s.situs_street
          ,CASE WHEN [situs_num]%2 = 0 THEN situs_num ELSE 9999999 END ASC
          ,CASE WHEN [situs_num]%2 <> 0 THEN situs_num ELSE -1 END DESC

If you can take the remainder of situs_num without first casting then it is already numeric. If not you would have to cast differently as follows

ORDER BY s.situs_street
          ,CASE WHEN CAST([situs_num] AS INT)%2 = 0 THEN  CAST([situs_num] AS INT) ELSE 9999999 END ASC
          ,CASE WHEN CAST([situs_num] AS INT)%2 <> 0 THEN  CAST([situs_num] AS INT) ELSE -1 END DESC
Comments