Order by issue, convoluted

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

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

---

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

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

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

---

:cool:

Apologies to longneck. :blush:

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

As anticipated - that's tickety-boo, rudy. :)

bazz

tickety-boo is what i do ™

:cool:

I know :)