Get an order request in a specific way

Question

I have a query that, when executed, returns all the clocks in the last 24 hours. Therefore, if you run the query at 10:55, the results will be from 11 AM yesterday to 10 AM.

Here is the query I'm 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 result I'm 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 I have a question, is there a way to order COL_X to get this particular output, since my current order by 2 usually does not return the result above.

Answer 1

I think you want to order by the actual date column (a_date). He will not allow you to order it yourself, because it is not used in a group. To resolve this, you need to somehow aggregate the date. Just using MAX or MIN can work.

In short, just ORDER BY MAX (a_date)

Answer 2

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

Answer 3

Yes, you can use the CASE statement inside an ORDER BY like this:

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

Answer 4

try the following:

Just change the order to

 Order by a_date 

Since a_date is a date column and it will chronologically order