Select a row having a column with max value - On a date range

Question

Excuse me for posting a similar question. Please consider this:

date                 value

18/5/2010, 1 pm        40
18/5/2010, 2 pm        20
18/5/2010, 3 pm        60
18/5/2010, 4 pm        30
18/5/2010, 5 pm        60
18/5/2010, 6 pm        25 
19/5/2010, 6 pm        300 
19/5/2010, 6 pm        450 
19/5/2010, 6 pm        375 
20/5/2010, 6 pm        250 
20/5/2010, 6 pm        310 

The query is to get the date and value for each day such that the value obtained for that day is max. If the max value is repeated on that day, the lowest time stamp is selected. The result should be like:

18/5/2010, 3 pm        60
19/5/2010, 6 pm        450
20/5/2010, 6 pm        310

The query should take in a date range like the one given below and find results for that range in the above fashion:

where date >= to_date('26/03/2010','DD/MM/YYYY') AND date < to_date('27/03/2010','DD/MM/YYYY')

Answer 1

I've not tried this, but I would think you want something like:

select max(date)
from  table
where date >= to_date('26/03/2010','DD/MM/YYYY') AND date < to_date('27/03/2010','DD/MM/YYYY')
group by trunc(date)

Answer 2

If you provide a CREATE TABLE and INSERT, it makes it a lot easier to provide a tested answer.

create table i (i_dt date, i_val number);

insert into i values (to_date('18/5/2010 1pm','dd/mm/yyyy hham'),        40);
insert into i values (to_date('18/5/2010 2pm','dd/mm/yyyy hham'),        20);
insert into i values (to_date('18/5/2010 3pm','dd/mm/yyyy hham'),        60);
insert into i values (to_date('18/5/2010 4pm','dd/mm/yyyy hham'),        30);
insert into i values (to_date('18/5/2010 5pm','dd/mm/yyyy hham'),        60);
insert into i values (to_date('18/5/2010 6pm','dd/mm/yyyy hham'),        25 );
insert into i values (to_date('19/5/2010 6pm','dd/mm/yyyy hham'),        300 );
insert into i values (to_date('19/5/2010 6pm','dd/mm/yyyy hham'),        450 );
insert into i values (to_date('19/5/2010 6pm','dd/mm/yyyy hham'),        375 );
insert into i values (to_date('20/5/2010 6pm','dd/mm/yyyy hham'),        250 );
insert into i values (to_date('20/5/2010 6pm','dd/mm/yyyy hham'),        310 );

select i_dt, i_val from 
   (select i.*, rank() over (partition by trunc(i_dt) order by i_val desc, i_dt asc) rn 
   from i) 
where rn = 1;

Answer 3

You are aggregating your data, so use grouping and aggregation functions. You can add any where clause you want, but I copied your where clause in, changing the dates so every record is selected. Borrowing Gary's create table and insert statements:

SQL> select min(i_dt) keep (dense_rank last order by i_val) i_dt
  2       , max(i_val) i_val
  3    from i
  4   where i_dt >= to_date('26/03/2010','dd/mm/yyyy')
  5     and i_dt < to_date('27/05/2010','dd/mm/yyyy')
  6   group by trunc(i_dt)
  7  /

I_DT                     I_VAL
------------------- ----------
18-05-2010 15:00:00         60
19-05-2010 18:00:00        450
20-05-2010 18:00:00        310

3 rows selected.

Regards, Rob.