CODEWITHSUNDEEP
sqloracle-databasesql-order-by
Pieter van Niekerk
Pieter van Niekerk

Reputation: 848

Get query to order in a specific way

I have a query that, when executed, returns all hours for the past 24 hours. So if you run the query at 10:55am the results will be from 11am yesterday up to 10am today.

Here is the query I am using:

select to_char(trunc(a_date,'HH24'),'HH24') col_x,
       type_name seriesname,
       sum(a_measure_3) col_y
from a_agg,
     (select trunc(sysdate, 'hh')-1 POS from dual)
where a_date >= POS
and   a_date <= POS + 1
group by trunc(a_date,'HH24'),
         type_name 
order by 2;

And this is the output that I am looking for:

COL_X  | SERIESNAME  | COL_Y
-----------------------------------
 11    |  STATUS1    |  6715846
 12    |  STATUS1    |  7064692
 13    |  STATUS1    |  6821742
 14    |  STATUS1    |  6241758
 15    |  STATUS1    |  6364713
 16    |  STATUS1    |  6762539
 17    |  STATUS1    |  6200213
 18    |  STATUS1    |  8479341
 19    |  STATUS1    |  13346516
 20    |  STATUS1    |  10107274
 21    |  STATUS1    |  3716290
 22    |  STATUS1    |  1321966
 23    |  STATUS1    |  433947
 00    |  STATUS1    |  200273
 01    |  STATUS1    |  97429
 02    |  STATUS1    |  81726
 03    |  STATUS1    |  124943
 04    |  STATUS1    |  284946
 05    |  STATUS1    |  1360847
 06    |  STATUS1    |  4250001
 07    |  STATUS1    |  5840156
 08    |  STATUS1    |  6666608
 09    |  STATUS1    |  6949196
 10    |  STATUS1    |  982411

Now my question is, is there a way to order by COL_X to get this specific output, as my current order by 2 wont always return the above result.

Upvotes: 4

Views: 226

Answers (4)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79919

Yes you can using CASE statement inside the ORDER BY clause something like:

 ORDER BY 
    CASE WHEN COL_X > 10 AND COL_X <= 23
         THEN 0
    ELSE 1
    END

Upvotes: 0

Chris
Chris

Reputation: 114

I think you are wanting to order by the actual date column (a_date). It won't allow you to order by it on its own as it is not used in the group by. To allow it, you need to aggregate the date somehow. Simply using a MAX or MIN could work.

So in short, just ORDER BY MAX(a_date)

Upvotes: 1

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

Wouldn't order by trunc(a_date,'HH24') be sufficient? You should be able to use all group-by values in order by, not just select-list expressions.

Upvotes: 3

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this:

Just change your order by clause to

Order by a_date

Since a_date is your date column and it will order chronologically

Upvotes: 0

Related Questions