Hi Guys,
I have a column in my database called “range”. Some example values are:
250 - 500
501 - 999
1000 - 1499
My question is now can I order by those ranges in ascending order (i.e. as above).
Thanks in advance.
Hi Guys,
I have a column in my database called “range”. Some example values are:
250 - 500
501 - 999
1000 - 1499
My question is now can I order by those ranges in ascending order (i.e. as above).
Thanks in advance.
not easily, no
however, if you were to replace your single varchar column with two integer ones, it’s a portion of pie
It’s possibly a bit dodgy but the separation in your values should prevent any mishaps
SELECT *, CONVERT(`range`, SIGNED) rangeStart FROM yourTable ORDER BY rangeStart
But I completely agree with Rudy that you should have a ‘beginning’ and ‘end’ field in there.
The above code is for MySQL
Try this:
SELECT `range`
FROM `range_tbl`
ORDER BY SUBSTRING_INDEX(`range`, '-', 1)*1 asc,
SUBSTRING_INDEX(SUBSTRING_INDEX(`range`, '-', 2), '-', -1)*1 - SUBSTRING_INDEX(`range`, '-', 1)*1 asc
range
--------------------------------------------
250 - 500
501 - 888
501 - 999
1000 - 1499
Assuming ‘-’ is the separator in the range, I’m ordering based on the first value in the range and, just for fun, based on the difference between the second value and the first one. It could simply be first value in the range and then the second value in the range.
I simply cast str to int by making str*1.
It’s MySQL. If you need to port it to another RDBMS, just let me know.
<hr>
You asked how it can be done. Performance penalties are another matter altogether.
The above can be altered further:
SELECT
`range`,
SUBSTRING_INDEX(`range`, '-', 1) rangeLow,
SUBSTRING_INDEX(`range`, '-', -1) rangeHigh
FROM
`range_tbl`
ORDER BY
rangeLow ASC,
rangeHigh ASC
Nice touch on the high value ordering btw.
Thanks.
There is one problem.
SELECT
`range`,
SUBSTRING_INDEX(`range`, '-', 1) rangeLow,
SUBSTRING_INDEX(`range`, '-', -1) rangeHigh
FROM
`range_tbl`
ORDER BY
rangeLow ASC,
rangeHigh ASC
returns this:
range rangeLow rangeHigh
1000 - 1499 1000 1499
250 - 500 250 500
501 - 999 501 999
501-888 501 888
You need to also type cast to int: *1
SELECT
`range`,
SUBSTRING_INDEX(`range`, '-', 1)*1 rangeLow,
SUBSTRING_INDEX(`range`, '-', -1)*1 rangeHigh
FROM
`range_tbl`
ORDER BY
rangeLow ASC,
rangeHigh ASC
Nice additional improvement on your part too. Much clearer.
Ah, of course!
*1 - neater than explicit casting.
Yeah, a rather old trick that served me well in more than one place, meaning not just with SQL. To be used with caution, though. You could also do +0, -0, /1, but some solid testing is required first before resorting to something like that in a new environment.