tricky ORDER BY

[IBazz]

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.

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

[IBazz]

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

[r937]

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...

SELECT age_group
     , FIELD(age_group,1) AS sortkey
 FROM default_age_groups

[IBazz]

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

[r937]

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

[IBazz]

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

[r937]

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 --

ORDER BY FIELD(age_group,1),'A','U','O'

you actually want this --

ORDER BY FIELD(LEFT(age_group,1),'A','U','O')

[IBazz]

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

[r937]

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?

[IBazz]

Blimey; I thought about adding a zero but thought it was not the best way.

Thanks Rudy, again.

[IBazz]

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