CODEWITHSUNDEEP
sqloracle-database
user1838000
user1838000

Reputation: 275

Sorting Range Values in Oracle SQL

I have a Case statement which will give me range values. I need to sort the result set accordingly in range wise order:

select distinct   CASE
     when Table__107.Column <= 30 then
      '0-30'
     when (Table__107.Column >= 31 and
          Table__107.Column <= 60) then
      '31-60'
     when (Table__107.Column >= 61 and
          Table__107.Column <= 90) then
      '61-90'
     when (Table__107.Column >= 91 and
          Table__107.Column <= 120) then
      '91-120'
     when (Table__107.Column >= 121 and
          Table__107.Column <= 180) then
      '121-180'
     when (Table__107.Column >= 181 and
          Table__107.Column <= 365) then
      '181-365'
     when Table__107.Column > 365 then
      '365+'
   end as Column

My required output is 

0-30
31-60
61-90
.....
.....
365+

flag

I tried using Order by 1 and Order By ASC , but it is sorting based on first character alphanumeric not number value of range.

Upvotes: 0

Views: 1007

Answers (3)

APC
APC

Reputation: 146209

Time buckets seem like the sort of data which deserve a reference data table. But even if you don't want to create a table for them there are benefits from treating them as one.

In this solution I use the WITH clause to create a sub-query with a sort number, lower and upper bounds, and a label for each time bucket. Doing this makes it easy to (say) count how instances of Table__107.col1 fall in to each bucket:

with buckets as ( 
     select 1 as bno,   0 as lb,    30 as ub, '0-30' as label from dual union all 
     select 2 as bno,  31 as lb,    60 as ub, '31-60' as label from dual union all 
     select 3 as bno,  61 as lb,    90 as ub, '61-90' as label from dual union all 
     select 4 as bno,  91 as lb,   120 as ub, '91-120' as label from dual union all 
     select 5 as bno, 121 as lb,   180 as ub, '121-180' as label from dual union all 
     select 6 as bno, 181 as lb,   365 as ub, '181-365' as label from dual union all 
     select 7 as bno, 365 as lb, 10000 as ub, '365+'  as label from dual )
select  count(t107.col1) as col1_cnt
       , b.label as bucket
from  buckets b
      left outer join Table__107 t107
          on t107.col1 between b.lb and b.ub
group by b.bno, b.label
order by b.bno     ;

There's no need to duplicate the logic. An additional benefit is that we can use an outer join, and so capture empty buckets.

Upvotes: 1

PKey
PKey

Reputation: 3841

Just create second column - with same criteria (cases) but with order number, and then order by that column.

Something like this:

select distinct   CASE
     when Table__107.Col <= 30 then
      '0-30'
     when (Table__107.Col >= 31 and
          Table__107.Col <= 60) then
      '31-60'
     when (Table__107.Col >= 61 and
          Table__107.Col <= 90) then
      '61-90'
     when (Table__107.Col >= 91 and
          Table__107.Col <= 120) then
      '91-120'
     when (Table__107.Col >= 121 and
          Table__107.Col <= 180) then
      '121-180'
     when (Table__107.Col >= 181 and
          Table__107.Col <= 365) then
      '181-365'
     when Table__107.Col > 365 then
      '365+'
   end as Col, CASE
     when Table__107.Col <= 30 then
      1
     when (Table__107.Col >= 31 and
          Table__107.Col <= 60) then
      2
     when (Table__107.Col >= 61 and
          Table__107.Col <= 90) then
      3
     when (Table__107.Col >= 91 and
          Table__107.Col <= 120) then
      4
     when (Table__107.Col >= 121 and
          Table__107.Col <= 180) then
      5
     when (Table__107.Col >= 181 and
          Table__107.Col <= 365) then
      6
     when Table__107.Col > 365 then
      7  end as ord 
    from Table__107
    order by ord

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269513

First, you could rename your ranges to be sortable . . . 000-030, '031-060', and so on. Then you can order directly by the value.

Next, you can simplify the logic, because you know that the case conditions are evaluated in order.

Finally, you can get what you want using group by and then ordering afterwords, say by the minimum value in each group:

select (case when Table__107.Column <= 30 then '0-30'
             when Table__107.Column <= 60 then '31-60'
             when Table__107.Column <= 90 then '61-90'
             when Table__107.Column <= 120 then '91-120'
             when Table__107.Column <= 180 then '121-180'
             when Table__107.Column <= 365 then '181-365'
             when Table__107.Column > 365 then '365+'
        end) as Column,
       count(*) -- this is a guess
from t
group by (case when Table__107.Column <= 30 then '0-30'
               when Table__107.Column <= 60 then '31-60'
               when Table__107.Column <= 90 then '61-90'
               when Table__107.Column <= 120 then '91-120'
               when Table__107.Column <= 180 then '121-180'
               when Table__107.Column <= 365 then '181-365'
               when Table__107.Column > 365 then '365+'
          end)
order by min(Table__107.Column);

Upvotes: 2

Related Questions