SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: order by issue

  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    order by issue

    Hi,

    the following query gets me the correct results however, The data stored makes the order by clause give me the output incorrectly.

    the values to be returned are like this, for example.

    10 The square
    9 The square
    7 The Square
    My Business

    I want to display it in numerical value and alphabetical order like this

    7 The Square
    9 The Square
    10 The Square
    My Business

    So, numeric first and alphabetical afterwards.

    Trouble is, 10 is seen as an earlier number than 7.

    What should I be looking at when making the numerical item output erm, in numeric order - 7, 8, 9, 10?

    here's the query so far

    Code MySQL:
    select id as sub_business_id
     , business as sub_business_name
     , parent_id as parent_id
     from businesses as b
     where (
             ( b.id = 123)
             or
             ( b.parent_id = 123 )
    	   )
        order by sub_business_name
    bazz

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    So, numeric first and alphabetical afterwards.
    dear bazz, how lovely to see you again after all these years
    Code:
    ORDER
        BY CASE WHEN 0 + SUBSTRING_INDEX(sub_business_name,' ',1) > 0
                THEN 'humpty'
                ELSE 'dumpty' END DESC
         , CASE WHEN 0 + SUBSTRING_INDEX(sub_business_name,' ',1) > 0       
                THEN RIGHT(CONCAT('00000000',
                         SUBSTRING_INDEX(sub_business_name,' ',1)),9)
                ELSE sub_business_name END
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi rudy,

    Thanks for the welcome back. you missed me a couple of times recently lol. been working away from the 'puter for a while and am a bit rusty. Still, even when sharp as a button, I never had the grasp of CASE.

    Thanks for the code. I'll work through it to see why it does humpty and dumpty and come back if I can't get it.

    bazz

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So, is the first case statement basically saying;

    ORDER
    BY
    # if the first char of the substring + 0 is greater than 0 put it in a group of 'humpty'
    CASE WHEN 0 + SUBSTRING_INDEX(sub_business_name,' ',1) > 0
    THEN 'humpty'
    # else, put them in a group called 'dumpty'
    ELSE 'dumpty' END DESC
    # and then he got stuck. what does the next bit do, please?
    , CASE WHEN 0 + SUBSTRING_INDEX(sub_business_name,' ',1) > 0
    THEN RIGHT(CONCAT('00000000',
    SUBSTRING_INDEX(sub_business_name,' ',1)),9)
    ELSE sub_business_name END

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    if the first char of the substring + 0 ...
    no, it's not the first character, it's the substring up to the first space

    Quote Originally Posted by IBazz View Post
    and then he got stuck.
    the humpties come before the dumpties because of the DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •