Reputation: 9752

how do I order a column by groups rather than numbers?

Hi I am using sql db2 to create age_bands as so:

case when age between 0 and 1 then '0-1'
when age between 2 and 3 then '2-3'
.
.
.
when age between 10 and 11 then '10-11'

which of course when you order you get the following:

0-1
10-11
2-3

which is not what i want. How do i order by the actual age_bands to get:

0-1
2-3
10-11

thanks very much.

Upvotes: 0

Answers (3)

WarrenT

Reputation: 4532

Order by age is simplest, as already stated.

However if you need to assign a band number or code, or need totals or statistics by band, or some other sort within the band, then you may want to approach this differently. You may want to have an AgeBand table, defining the age range, perhaps the band number or code, perhaps a name for the age band, or other "master" data.

If that is the case you would get your band code with a

left join AgeBand ab   on details.age >= ab.FromAge and details.age <= ab.ToAge
...
order by ab.BandID, ...

But if you don't need the AgeBand table, and would like to continue to compute the age band code with a CASE statement, that is fine too, and may be all you need.

What you can do is put your current query in a common table expression, or CTE, to prepare your data, then continue the SELECT statement, using the name you assigned to the CTE as your from-table.

WITH q AS
(SELECT ...
        CASE WHEN ...  THEN "00-01"
             WHEN ...  THEN "02-03"
             ...
             WHEN ...  THEN "10-11"
        END as bandcode
   FROM yourinputdata as i
   WHERE ...
)
SELECT bandcode, ...
  FROM q
  ORDER BY bandcode, ...
;

The WITH and the query between the quotes is the common table expression. It is given a name "q" in the first line, and then referred to at the bottom. It is the bottom full-select that controls your final results.

Upvotes: 1