CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
John Wick
John Wick

Reputation: 745

How to grab the DATE that the MAX occurred?

So i have a table called Value. This table has columns called: VALUE_ID, VALUE, HR, VALUE_TYPE. I am trying to grab not only the maximum value but also the HR (and ultimately the day) that the Maximum Value Occurred.

Below is some sample data:

VALUE_ID      VALUE     HR                           VALUE_TYPE
   1            75      DEC-25-2018 01:00:00 AM      Bananas
   2            10      DEC-25-2018 01:00:00 AM      Bananas
   3            787     DEC-25-2018 05:00:00 PM      Bananas

I want:

 (For Hourly)
 MAX(Value)         HR                          Value_Type
   75               DEC-25-2018 01:00:00 AM     Bananas
   787              DEC-25-2018 05:00:00 PM     Bananas

 (For Day)
 MAX(Value)         HR(Day)                     Value_Type
   787              DEC-25-2018 05:00:00 PM     Bananas

I've tried the following (this is probably completely wrong but im not sure how to combine columns from two separate queries into one table):

select max(value) as max_value
    , Value_Type
 from value
group by value_type
UNION
select HR from
 from value 
where value = (select max(value) as max_value
        , Value_Type
 from value
group by value_type;

Thanks in advance.

Upvotes: 1

Views: 395

Answers (2)

user5683823
user5683823

Reputation:

Analytic functions are perfect for this kind of question. They allow the base data to be read just once (rather than multiple times, as in solutions that aggregate, then compare to the original data).

In the sample session below, notice a few things. I create some sample data in a WITH clause (which you don't need, you have the actual table). I use the TO_DATE function to create dates. So that I don't need to write the format model multiple times, I first alter my session to set the default date format to the one you used.

The query is written for the hourly intervals; you can modify it easily for daily, by changing the argument to TRUNC() in the inner query, from 'hh' to 'dd'. If you are new to analytic functions, select and run the inner query by itself first, to see what it produces. Then it will be trivial to understand what the outer query does.

alter session set nls_date_format = 'MON-dd-yyyy hh:mi:ss AM';

with simulated_table (VALUE_ID, VALUE, HR, VALUE_TYPE) as (
  select 1,  75, to_date('DEC-25-2018 01:00:00 AM'), 'Bananas' from dual union all
  select 2,  10, to_date('DEC-25-2018 01:00:00 AM'), 'Bananas' from dual union all
  select 3, 787, to_date('DEC-25-2018 05:00:00 PM'), 'Bananas' from dual
)
select value_id, value, hr, value_type
from   (
         select s.*,
                max(value) over (partition by value_type, trunc(hr, 'hh')) maxval
         from   simulated_table s
       )
where  value = maxval
;

  VALUE_ID      VALUE HR                      VALUE_TYPE
---------- ---------- ----------------------- ----------
         1         75 DEC-25-2018 01:00:00 AM Bananas
         3        787 DEC-25-2018 05:00:00 PM Bananas

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You could do:

select v.*
from value v
where v.value = (select max(v2.value)
                 from value v2
                 where trunc(v2.hr, 'HH')= trunc(v.hr, 'HH')
                ) or
      v.value = (select max(v2.value)
                 from value v2
                 where trunc(v2.hr, 'DD')= trunc(v.hr, 'DD')
                ) ;

This gets the maximum value rows for both the hour and the day. You can use one clause or the other for just hours or just days.

Upvotes: 2

Related Questions