SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Hybrid View

  1. #1
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Order by question

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    not easily, no

    however, if you were to replace your single varchar column with two integer ones, it's a portion of pie
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    It's possibly a bit dodgy but the separation in your values should prevent any mishaps
    Code sql:
    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
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  4. #4
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Try this:

    Code:
    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. #5
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    The above can be altered further:
    Code SQL:
    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.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  6. #6
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Thanks.

    There is one problem.

    Code SQL:
    SELECT
        `range`,
        SUBSTRING_INDEX(`range`, '-', 1) rangeLow,
        SUBSTRING_INDEX(`range`, '-', -1) rangeHigh
    FROM
        `range_tbl`
    ORDER BY
        rangeLow ASC,
        rangeHigh ASC

    returns this:
    Code:
    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

    Code:
    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. #7
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Ah, of course!

    *1 - neater than explicit casting.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  8. #8
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •