SitePoint Sponsor

User Tag List

Results 1 to 11 of 11

Thread: tricky ORDER BY

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

    tricky ORDER BY

    Hi,

    I am retrieving the results from the db but they are not properly ordered. I need ordering to be done on two levels, if possible.

    Code MySQL:
    SELECT 
             age_group
     FROM default_age_groups
       ORDER  
          BY FIELD(age_group,1),'A','U','O'

    here is the data as returned:

    Adult
    Up to 3 yrs
    Up to 12 yrs
    Up to 10 yrs
    Over 65 yrs
    Over 60 yrs
    Over 55 yrs
    Over 21 yrs
    Over 18 yrs
    Up to 5 yrs
    As you can see it is not even ordered as I am trying in the query because 'Up to 5 yrs' is at the end. In any event, I need it to be ordered by A,U and O and within that, for it to be ordered by the numbers.

    Can anyone point to a solution, please.

    bazz

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    reading some more, I think someone may tell me I am actually storing multiple values in a string, which breaks normalisation rules.

    Am I correct? If I need to split the string across several cols I'm gonna get a headache working out an easy UI for inputting their ages.

    bazz

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i don't see where you are storing multiple values in a string

    but i can definitely tell you how your ORDER BY works

    would you run this query for me please...
    Code:
    SELECT age_group
         , FIELD(age_group,1) AS sortkey
     FROM default_age_groups
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the response rudy,

    The results are in correct alphabetical order.

    Adult 0
    Over 18 yrs 0
    Over 21 yrs 0
    Over 55 yrs 0
    Over 60 yrs 0
    Over 65 yrs 0
    Up to 10 yrs 0
    Up to 12 yrs 0
    Up to 3 yrs 0
    Up to 5 yrs 0
    Not quite sure where the 0 (zero) comes into it but it is part of the returned array.

    bazz

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    Not quite sure where the 0 (zero) comes into it
    the 0 comes from FIELD(age_group,1)

    which is what i wanted you to see

    it's 0 for every single row

    now imagine this in the ORDER BY and the effect it would have

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I don't see how it could be used to order anything so I think I have not understood that part of ORDER BY.

    I had thought of a case statement but I am trying to order on two separate values so I don't think that is the correct approach.

    bazz

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    okay, it wasn't obvious, so i'll spell it out -- your ORDER BY was pants

    remember tickety-boo is what i do ™ ?

    you asked an almost identical ORDER BY question before

    instead of this --
    Code:
    ORDER BY FIELD(age_group,1),'A','U','O'
    you actually want this --
    Code:
    ORDER BY FIELD(LEFT(age_group,1),'A','U','O')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah ok. I thought that the left was used when I want using a join.

    so that brings me back this order:
    Adult:
    Over 18 yrs:
    Over 21 yrs:
    Over 55 yrs:
    Over 60 yrs:
    Over 65 yrs:
    Up to 10 yrs:
    Up to 12 yrs:
    Up to 3 yrs:
    Up to 5 yrs:
    The 'Up to' ages are not as I want. I want them as 3,5,10,12. What's the word I should be looking for in the manual?

    bazz

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    Ah ok. I thought that the left was used when I want using a join.
    LEFT(string,1) is the same as SUBSTRING(string FROM 1 FOR 1)

    as for your problem, try sticking a space in front of the 3 and the 5



    by the way, how come you have a separate category for adult, when the "over" values cover all adult ages?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Blimey; I thought about adding a zero but thought it was not the best way.

    Thanks Rudy, again.

  11. #11
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    by the way, how come you have a separate category for adult, when the "over" values cover all adult ages?
    Coz we need the choice.

    bazz


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
  •