Oracle query for time range

Question

The below is a practice for myself:

I've created a table with some values, as:

CREATE TABLE my_test AS
SELECT ROWNUM ID, TRUNC(SYSDATE)+(LEVEL*5/24/60/60)date_time , 111 person_id
FROM dual CONNECT BY LEVEL <= (24*60*60)/5 
ORDER BY 1;

Now, I updated table , person_id = 222 for date_time between 5:30 am and 6:30 am

UPDATE my_test
SET  person_id   = 222
WHERE date_time >= trunc(SYSDATE)+ 5/24 + 30/24/60
 AND DATE_TIME  <= trunc(sysdate)+ 6/24 + 30/24/60;

I need to take the number of persons in each hour, so I've written a code like:

SELECT time_range,count(distinct person_id) PERSON_COUNT
FROM (
select person_id , date_time,
case TO_CHAR(date_time,'hh24') when '00' then '12 AM - 01 AM' 
             when '01' then '01 AM - 02 AM'
             when '02' then '02 AM - 03 AM'
             when '03' then '03 AM - 04 AM'
             when '04' then '04 AM - 05 AM'
             when '05' then '05 AM - 06 AM'
             when '06' then '06 AM - 07 AM'
             when '07' then '07 AM - 08 AM'
             when '08' then '08 AM - 09 AM'
             when '09' then '09 AM - 10 AM'
             when '10' then '10 AM - 11 AM'
             when '11' then '11 AM - 12 PM'
             when '12' then '12 PM - 01 PM'
             when '13' then '01 PM - 02 PM'
             when '14' then '02 PM - 03 PM'
             when '15' then '03 PM - 04 PM'
             when '16' then '04 PM - 05 PM'
             when '17' then '05 PM - 06 PM'
             when '18' then '06 PM - 07 PM'
             when '19' then '07 PM - 08 PM'
             when '20' then '08 PM - 09 PM'
             when '21' then '09 PM - 10 PM'
             when '22' then '10 PM - 11 PM'
             WHEN '23' THEN '11 PM - 12 AM' end time_range
FROM my_test
WHERE date_time >= trunc(SYSDATE)
AND date_time < trunc(SYSDATE)+1
ORDER BY date_time)
GROUP BY time_range;

Its working fine without expected ordering. ( The order by time_range not gives the expected as it is string in the outer query).

I need to order by time_range, and any simple and easy codes for the above are appreciated. Thanks in advance.

Vincent Malgrat · Accepted Answer · 2013-09-11 12:24:27Z

2

The ORDER BY clause is always the last to be interpreted in a single query, here however it is in a subquery so the main query GROUP BY will "overwrite" your ORDER BY.

You could write for instance:

SELECT time_range, COUNT(DISTINCT person_id) PERSON_COUNT
  FROM (SELECT person_id,
               date_time,
               CASE TO_CHAR(date_time, 'hh24')
                  WHEN '00' THEN
                   '12 AM - 01 AM'
               /*[snip]*/
                  WHEN '23' THEN
                   '11 PM - 12 AM'
               END time_range
          FROM my_test
         WHERE date_time >= trunc(SYSDATE)
           AND date_time < trunc(SYSDATE) + 1)
 GROUP BY time_range, TO_CHAR(date_time, 'hh24')
 ORDER BY TO_CHAR(date_time, 'hh24');

Also I'm not a fan of your time_range expression. You could rewrite it simply as:

to_char(date_time, 'HH PM - ') || to_char(date_time + 1/24, 'HH PM') time_range

Edit: apparently you need the complete query:

SELECT time_range, COUNT(DISTINCT person_id) PERSON_COUNT
  FROM (SELECT person_id,
               date_time,
               to_char(date_time, 'HH PM - ') 
               || to_char(date_time + 1/24, 'HH PM')  time_range
          FROM my_test
         WHERE date_time >= trunc(SYSDATE)
           AND date_time < trunc(SYSDATE) + 1)
 GROUP BY time_range, TO_CHAR(date_time, 'hh24')
 ORDER BY TO_CHAR(date_time, 'hh24');

edited Sep 11, 2013 at 12:24

answered Sep 11, 2013 at 11:15

Vincent Malgrat

67.9k9 gold badges122 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ajmalmhd04 Over a year ago

wow. Thats so simple. Ive used:

SELECT COUNT(DISTINCT person_id) ,      time_range FROM      (SELECT date_time, person_id,TO_CHAR(date_time, 'HH PM - ')           || TO_CHAR(date_time + 1/24, 'HH PM') time_range      FROM my_test      ) GROUP BY time_range;

but still the ordering is not done in this logic

Vincent Malgrat Over a year ago

You need an ORDER BY clause. Ordering is never done by magic.

ajmalmhd04 Over a year ago

you can see that ORDER BY will not make effect for the above comment I used, ie; according to your second simpler query. Please check it.

Vincent Malgrat Over a year ago

Can't you, like, combine the two elements of my answer? =)

Gordon Linoff · Accepted Answer · 2013-09-11 11:14:13Z

1

First, you should have the order by in the outer query rather than the inner query. order by in inner queries is generally not guaranteed to work.

But, even if you put:

order by date_time;

at the end, you still won't get what you want. For that, try ordering by the date_time itself, as in:

order by max(date_time);

Here is an example of the Oracle documentation on order by in subqueries:

An ORDER BY clause allows you to specify the order in which rows appear in the result set. In subqueries, the ORDER BY clause is meaningless unless it is accompanied by one or both of the result offset and fetch first clauses or in conjunction with the ROW_NUMBER function, since there is no guarantee that the order is retained in the outer result set. It is permissible to combine ORDER BY on the outer query with ORDER BY in subqueries.

answered Sep 11, 2013 at 11:14

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

ajmalmhd04 Over a year ago

Thanks for the info about sub-queries, but I am looking for ordering the outer query in the mentioned time_range

DB_learner · Accepted Answer · 2013-09-11 11:24:52Z

0

select case TO_CHAR(date_time,'hh24') when '00' then '12 AM - 01 AM' 
         when '01' then '01 AM - 02 AM'
         when '02' then '02 AM - 03 AM'
         when '03' then '03 AM - 04 AM'
         when '04' then '04 AM - 05 AM'
         when '05' then '05 AM - 06 AM'
         when '06' then '06 AM - 07 AM'
         when '07' then '07 AM - 08 AM'
         when '08' then '08 AM - 09 AM'
         when '09' then '09 AM - 10 AM'
         when '10' then '10 AM - 11 AM'
         when '11' then '11 AM - 12 PM'
         when '12' then '12 PM - 01 PM'
         when '13' then '01 PM - 02 PM'
         when '14' then '02 PM - 03 PM'
         when '15' then '03 PM - 04 PM'
         when '16' then '04 PM - 05 PM'
         when '17' then '05 PM - 06 PM'
         when '18' then '06 PM - 07 PM'
         when '19' then '07 PM - 08 PM'
         when '20' then '08 PM - 09 PM'
         when '21' then '09 PM - 10 PM'
         when '22' then '10 PM - 11 PM'
         WHEN '23' THEN '11 PM - 12 AM' end time_range,
count(person_id) person_count
FROM my_test
WHERE date_time >= trunc(SYSDATE)
AND date_time < trunc(SYSDATE)+1 
group by TO_CHAR(date_time,'hh24')
ORDER BY 1

answered Sep 11, 2013 at 11:24

DB_learner

1,0369 silver badges15 bronze badges

1 Comment

ajmalmhd04 Over a year ago

Thanks, for the info for usage of group by its, correct but the you need to use count( distinct person_id) instead. and anyway I've got simpler query by @Vincent

Collectives™ on Stack Overflow

Oracle query for time range

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related