How to grab the DATE that the MAX occurred?

Question

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.

What do you need to show if (let's say for the "day" query) you geth the same MAX value TWICE (or more!), at different times (different hours)? Do you need the earliest time the MAX was achieved? The latest time? One of them, no matter which one? Or ALL of them? (which requires a different approach) — , Aug 14 '18 at 21:37
I guess all of the times in the case of a duplicate MAX occuring in that time period. If thats not possible, the first one. — John Wick, Aug 15 '18 at 17:32

Gordon Linoff · Answer 1 · 2018-08-14T21:49:32.217

2

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.

edited Aug 14 '18 at 21:49

answered Aug 14 '18 at 20:59

Gordon Linoff

1,242,037
58
646
786

thank you Gordon Linoff. I'm trying to figure out the HR (hour and day) that the max occured. – John Wick Aug 15 '18 at 17:33
I'm trying to figure out the HR (hour and day) that the max occured. What do i do if i want to group all of these maxes by day, week, month, year, etc.? – John Wick Aug 15 '18 at 17:38
Also what happens if im adding more columns that i need to group by in the inner query (i.e. Person_Name)? – John Wick Aug 15 '18 at 17:44
@JohnWick . . . Then you would have a different question. And new questions should be asked as *questions*, not *comments*. – Gordon Linoff Aug 16 '18 at 01:32
okay thank you! just wanted to make sure I don't upset anyone by asking a similar question. (haha) – John Wick Aug 16 '18 at 13:46

score 2 · Accepted Answer · answered Aug 15 '18 at 18:56

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

not sure if this question is related (apologies in advance) what if we run into the issue where we return 2 or more of the same 'max' value? — John Wick, Aug 16 '18 at 13:48

How to grab the DATE that the MAX occurred?

2 Answers2