CODEWITHSUNDEEP
sqloracle-sqldeveloper
wbrugato
wbrugato

Reputation: 1469

Get max value while retaining other columns in SQL

I have a table with columns: STARTDATE, ENDDATE, ID, A, B, C (picture shows different names but idea is the same)

the startdate and enddate are accross multiple days and include data for each hour. Also for each startdate/enddate there are multiple ID's. Example of what a piece of the table looks like:

http://i58.tinypic.com/300qsn4.png

What I want to do is get a daily max for each ID of the value C and preserve the other values in the row where this daily max occurred.

So far I am doing something like this, however for this very large data set, this will usually time out:

select * from table
where (Startdate, Enddate, ID, A, B, C) not in(select * from table
where (ID, C) not in(select ID, max(C) from table group by ID));

Upvotes: 2

Views: 382

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270443

You can try using row_number(). Assuming you can use startdate as the date indicator:

select t.*
from (select t.*,
             row_number() over (partition by id, trunc(startdate) order by c desc) as seqnum
      from table t
     ) t
where seqnum = 1;

If you need to get this for spans of time, I would suggest joining in numbers to get the different dates and joining to get values on each date. For instance, if the time span for a record is never more than 9 days:

with nums as (
      select level - 1 as n
      from dual
      connect by level <= 10
)
select t.*
from (select t.*,
             row_number() over (partition by id, trunc(startdate + n.n) order by c desc) as seqnum
      from table t join
           nums
           on startdate + nums.n <= enddate
     ) t
where seqnum = 1;

Upvotes: 2

Related Questions