CODEWITHSUNDEEP
sqloracle-database
John Wick
John Wick

Reputation: 745

How to grab the hour of the MAX value of a (Day's worth) set of data?

Let's say that I have already calculated the maximum number of fruit sold in a day (stored in the value column.) I need to have the time_of_day (i.e. 3:00 PM) that this maximum value occurs. How would I be able to do that without having to also group by time_of_day (since that would throw the grouping off). Below is an example of what I would start out with:

 Value        Data_date   Name      Hour
   7          7/17/2018    A        2:00 AM
  15          7/17/2018    A        4:00 AM
  25          7/17/2018    A        7:00 PM
  55          7/18/2018    B        1:00 AM
  17          7/18/2018    B        4:00 AM

Below is what I want:

  MAX(Value)  Data_date  Name     Hour
  25          7/17/2018   A       7:00 PM
  55          7/18/2018   B       1:00 AM

Below is what i tried:

select max(value)
     , data_date
     , name
     , hour
  from table
  group by value, grouping sets(data_date, name), grouping sets(hour, name);

Based off what I've dug up online, I think I will have to group by sets but not exactly sure which sets I need to group on ...

Thanks in advance!

Upvotes: 2

Views: 1676

Answers (2)

Alex Poole
Alex Poole

Reputation: 191305

You can do this with:

select max(value) as value,
  data_date,
  max(name) keep (dense_rank last order by value) as name,
  max(hour) keep (dense_rank last order by value) as hour
from your_table
group by data_date;

For each data_date, it gets the highest value using a simple aggregate, and the corresponding name and hour using last().

With your sample data as a CTE, and using the data types you said you are using:

-- cte for sample data
with your_table (value, data_date, name, hour) as (
            select  7, '7/17/2018', 'A', timestamp '2018-07-17 02:00:00' from dual
  union all select 15, '7/17/2018', 'A', timestamp '2018-07-17 04:00:00' from dual
  union all select 25, '7/17/2018', 'A', timestamp '2018-07-17 19:00:00' from dual
  union all select 55, '7/18/2018', 'B', timestamp '2018-07-18 01:00:00' from dual
  union all select 17, '7/18/2018', 'B', timestamp '2018-07-18 04:00:00' from dual
)
-- actual query
select max(value) as value,
  data_date,
  max(name) keep (dense_rank last order by value) as name,
  to_char(max(hour) keep (dense_rank last order by value), 'HH:MI AM') as hour
from your_table
group by data_date;

     VALUE DATA_DATE N HOUR    
---------- --------- - --------
        25 7/17/2018 A 07:00 PM
        55 7/18/2018 B 01:00 AM

If the date part of hour always matches the string value you have for data_date you could use that instead:

select max(value) as value,
  to_char(trunc(hour), 'MM/DD/YYYY') as data_date,
  max(name) keep (dense_rank last order by value) as name,
  to_char(max(hour) keep (dense_rank last order by value), 'HH:MI AM') as hour
from your_table
group by trunc(hour);

     VALUE DATA_DATE  N HOUR    
---------- ---------- - --------
        25 07/17/2018 A 07:00 PM
        55 07/18/2018 B 01:00 AM

You should also consider what you want to show if the same value appears for more than one hour on a day, which seems feasible. These will show arbitrary matching values, but you could add something to the order by clauses to pick say the earliest or latest matching hour. If you want to show all matches then you'd need a different approach, such as either of @Yogesh's...

Upvotes: 3

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use :

select t.*
from table t
where value = (select max(t1.value) 
               from table t1 
               where t1.name = t.name and
                     t1.data_date = t.data_date
              );

However, you can also use row_number() function :

select t.*
from (select *, row_number () over (partition by name, data_date order by value desc) as seq
      from table t
     ) t
where seq = 1;

Upvotes: 2

Related Questions