Order SQL query manually when numeric string representation is out of order

In some instances, doing a straight ORDER BY ASC (OR DESC) does not work as one might want:
0-50
101-200
201-300
301-400
401-500
501-600
51-100
601+
That is not how humans do it 😉

A way to get around this, is to add a new column and put in an ordering value, for instance:
1. 0-50
2. 51-100
3. 101-200
4. 201-300
5. 301-400
6. 401-500
7. 501-600
8. 601+
Now that is much better 🙂

Here is a real world example of this in action:

Order/Rank by your own criteria

Order/Rank by your own criteria


SELECT TOP (100) PERCENT
range AS [Distance Range],
COUNT(*) AS Customer
FROM (SELECT
CASE
WHEN distance_in_miles BETWEEN 0 AND 50 THEN ‘0-50′
WHEN distance_in_miles BETWEEN 51 AND
100 THEN ’51-100’
WHEN distance_in_miles BETWEEN 101 AND 200 THEN ‘101-200’
WHEN distance_in_miles BETWEEN 201 AND
300 THEN ‘201-300’
WHEN distance_in_miles BETWEEN 301 AND 400 THEN ‘301-400’
WHEN distance_in_miles BETWEEN 401 AND
500 THEN ‘401-500’
WHEN distance_in_miles BETWEEN 501 AND 600 THEN ‘501-600’
ELSE ‘601+’
END AS range,
CASE
WHEN distance_in_miles BETWEEN
0 AND 50 THEN ‘1’
WHEN distance_in_miles BETWEEN 51 AND 100 THEN ‘2’
WHEN distance_in_miles BETWEEN 101 AND
200 THEN ‘3’
WHEN distance_in_miles BETWEEN 201 AND 300 THEN ‘4’
WHEN distance_in_miles BETWEEN 301 AND
400 THEN ‘5’
WHEN distance_in_miles BETWEEN 401 AND 500 THEN ‘6’
WHEN distance_in_miles BETWEEN 501 AND
600 THEN ‘7’
ELSE ‘8’
END AS rank
FROM dbo.Reports_GeoCustomers) AS t
GROUP BY range,
rank
ORDER BY rank

Leave a Reply

Your email address will not be published. Required fields are marked *