Order by issue, convoluted

**IBazz** · Dec 7, 2009 16:28

Hello,

Below is a complete section of my query. I am trying to order by t.age_group but the values don't lend themselves to being ordered the way I want it to be ordered.

The values can begin with O, R or U and I need to order them like this:

R, O, U,

Should I make a new table column for an ordering value, which can be assigned when the record is created or, can this query be amended to do what I need, with the existing data?

Code MySQL:

LEFT OUTER
  JOIN ( SELECT live_product_id 
              , GROUP_CONCAT(
                       CONCAT_WS(','
                           , t.age_group
                           , t.tariff
                           , t.tariff_terms_abbr
                           , t.currency
                           , tt.tariff_terms
                             ) ORDER
                                  BY t.age_group
                  SEPARATOR ';'
                               ) AS tariffs
           FROM tariffs as t
           inner join tariff_terms as tt
           on t.tariff_terms_abbr = tt.tariff_terms_abbr
         GROUP
            BY live_product_id
                                    ) AS tb

Bazz

**crmalibu** · Dec 7, 2009 18:10

You can use something like

Code:

order by case c
when 'R' then 1
when 'O' then 2
when 'U' then 3
else 4
end desc

**IBazz** · Dec 7, 2009 19:43

I seem to recall a post from the past, by longneck, which basically said you can't manipulate returned data in an order by clause.

The R, O and U is the first letter of each value to be returned.

So I think I need to try a case in my sub select.

I'll post back if/when I get stuck.

bazz

**IBazz** · Dec 7, 2009 20:10

Haven't been able even to think through what the query should be like so I changed the value of my col so that the current 'order by' works.

I know - chicken

bazz

**r937** · Dec 7, 2009 22:05

you can't manipulate returned data in an ORDER BY clause?

that couldn't've been longneck, because of course you certainly can

Code:

ORDER BY FIELD(LEFT(t.age_group,1),'R','O','U'), t.age_group

**IBazz** · Dec 7, 2009 22:26

Apologies to longneck.

I had moved on in my research and was looking into regexes, where a similar question received that answer. not relevant to my OP.

@rudy,

Thank you for that code. I'll go try it out now.

bazz