SitePoint Sponsor |
|
User Tag List
Results 1 to 11 of 11
Thread: tricky ORDER BY
-
Dec 13, 2009, 19:32 #1
- 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.
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
Can anyone point to a solution, please.
bazz
-
Dec 13, 2009, 20:03 #2
- 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 headacheworking out an easy UI for inputting their ages.
bazz
-
Dec 13, 2009, 20:58 #3
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Dec 13, 2009, 22:04 #4
- 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
bazz
-
Dec 13, 2009, 22:26 #5
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Dec 13, 2009, 22:31 #6
- 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
-
Dec 13, 2009, 22:39 #7
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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'
Code:ORDER BY FIELD(LEFT(age_group,1),'A','U','O')
-
Dec 13, 2009, 22:47 #8
- 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:
bazz
-
Dec 13, 2009, 23:08 #9
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Dec 13, 2009, 23:12 #10
- 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.
-
Dec 13, 2009, 23:14 #11
- 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?
bazz
Bookmarks