What I have is a mix of numbers and ranges in a column like so
Range
---------
1
3-4
4
5-10
8-9
12-20
2
7
9
11-14
6
10
What I want is them to be ordered in the following manner
Range
---------
1
2
3-4
4
5-10
6
7
8-9
9
10
11-14
12-20
Won't work - The simple ORDER BY "Range" produces the following (as expected)
Range
---------
1
10
11-14
12-20
2
3-4
4
5-10
6
7
8-9
9
Is there a "simple" way for me to make it order the values (ranges and numbers) as I want them to?
You can use such query:
SELECT *
FROM YourTableName
ORDER BY
CAST(
CASE WHEN position('-' in Range) > 0
THEN substr(range, 0, position('-' in Range))
ELSE Range
END
as integer)
In your ORDER BY statement you first extract a number from your range:
position('-' in Range) > 0 is met, this means that you have a '-' somewhere in your string, so you should take a number until '-', which is achieved using substr(range, 0, position('-' in Range)). E.g. if you have 10-14 in your column, this step only takes 10.Range.After the above checks are done, you the number. In this case you cast this value to integer, so it correctly orders number (i.e. so 10 comes after 2, etc.)
The above query assumes that you always have the data in the format you specified - either number only or number-number. It would fail for other cases.
Test String etc, then it won't work, would it?!
Given your actual question, this is most easily done using split_part():
order by cast(split_part(range, '-', 1) as int)
This, of course, assumes that the values in the string are numeric. If they are not, then ask another question, with example values and how they should be sorted.
