Grabbing certain columns from the rows that the MAX value occurred

So i am trying to insert only the rows that are correlated to the HOUR where the MAX value occurred . However I am grouping by Day, and later on will be grouping on week, month, year, etc. Is my approach correct? The reason I am using a subquery is because I need to grab the value that goes into the HR column but adding it to the GROUP BY will mess up my daily groupings (if that makes sense). I need certain values from those columns to insert with a Stored Procedure that I wrote. Is the query I have below on the right track? Thanks in Advance

Below is some sample data:

VALUE_ID      VALUE     HR                           VALUE_TYPE   OFFSET  DATA_CODE
   1            75      DEC-25-2018 01:00:00 AM      Bananas        1    HI
   2            10      DEC-25-2018 02:00:00 AM      Bananas        1    HI
   3             0      DEC-25-2018 03:00:00 AM      Bananas        1    HI
   4            77      DEC-25-2018 04:00:00 AM      Bananas        1    HI
   5            787     DEC-25-2018 05:00:00 PM      Bananas        1    HI

What I want:

VALUE_ID      VALUE     HR                           VALUE_TYPE   OFFSET  DATA_CODE
   5            787     DEC-25-2018 05:00:00 PM      Bananas        1    HI  


SELECT v.value AS MAX_VALUE
     , v.offset
     , v.data_code
     , v.hr
     , v.code
     , v.data_date
     , to_date(to_char(to_date(lpad(v.data_date, 7, 0), 'DDDYYYY'), 'MM/DD/YYYY'), 'MM/DD/YYYY') as converted_date
FROM value v
inner join sub_value sv on v.value_id = sv.value_id
Where v.Value_id IN (select VALUE_ID from(
select  MAX(v.value) as MAX_VALUE
      , MAX(v.offset) as OFFSET
      , v.data_Code
      , MAX(v.value_id) as VALUE_ID
      , v.code
      , v.data_date
      , to_date(to_char(to_date(lpad(v.data_date, 7, 0), 'DDDYYYY'), 'MM/DD/YYYY'), 'MM/DD/YYYY') as converted_date
  from value v
  inner join sub_value sv on v.value_id = sv.value_id;

Also: Would the process be the same for weekly MAX as well? (of course I am going to convert the date/timestamp to IW (week), but aside from that)

Answer

One simple method uses row_number() or rank(). Here is an example, assuming you want the maximum for each data_code:

select t.*
from (select t.*, row_number() over (partition by data_code order by value desc) as seqnum
      from t
     ) t
where seqnum = 1;

Advertisement

Answer