Order by question

Databases
1

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.

2

not easily, no

however, if you were to replace your single varchar column with two integer ones, it’s a portion of pie :wink:

3

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.

Edit:

The above code is for MySQL

4

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.

5

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.

6

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.

7

Ah, of course!

*1 - neater than explicit casting.

8

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.