Re: SQL Question

From: Brian E Dick <bdick_at_cox.net>
Date: Wed, 29 Jan 2003 18:57:18 GMT
Message-ID: <iuVZ9.53770$GX4.2145198_at_news2.east.cox.net>


Something like the following.

select my_datetime, my_value
from my_table mt1
where my_value = (
select max(my_value)
from my_table mt2
where mt2.my_datetime between trunc(mt1.my_datetime) and trunc(mt1.my_datetime) + 1)

"Edd Stanley" <edd.stanley_at_verizonwireless.com> wrote in message news:8f863cad.0301291022.1b3bbd23_at_posting.google.com...
> I have a question about a SQL query that I have been trying to figure
> out for a very long time....
> I have a table that looks something like:
> my_datetime my_value
> 2003-JAN-24 01:00 20432
> 2003-JAN-24 02:00 10603
> 2003-JAN-24 03:00 5672
> 2003-JAN-24 04:00 3488
> 2003-JAN-24 05:00 4955
> 2003-JAN-24 06:00 12280
> 2003-JAN-24 07:00 30329
> 2003-JAN-24 08:00 54752
> 2003-JAN-24 09:00 75463
> 2003-JAN-24 10:00 87606
> 2003-JAN-24 11:00 97903
> 2003-JAN-24 12:00 102587
> 2003-JAN-24 13:00 105373
> 2003-JAN-24 14:00 110451
> 2003-JAN-24 15:00 124461
> 2003-JAN-24 16:00 136991
> 2003-JAN-24 17:00 147049
> 2003-JAN-24 18:00 125755
> 2003-JAN-24 19:00 99835
> 2003-JAN-24 20:00 80444
> 2003-JAN-24 21:00 106085
> 2003-JAN-24 22:00 92294
> 2003-JAN-24 23:00 67334
> 2003-JAN-25 00:00 44983
> 2003-JAN-25 01:00 27938
> 2003-JAN-25 02:00 17020
> 2003-JAN-25 03:00 10120
> 2003-JAN-25 04:00 6286
> 2003-JAN-25 05:00 4347
> 2003-JAN-25 06:00 5427
> 2003-JAN-25 07:00 11845
> 2003-JAN-25 08:00 31894
> 2003-JAN-25 09:00 62143
> 2003-JAN-25 10:00 90879
> 2003-JAN-25 11:00 111746
> 2003-JAN-25 12:00 120989
> 2003-JAN-25 13:00 116355
> 2003-JAN-25 14:00 112700
> 2003-JAN-25 15:00 115364
> 2003-JAN-25 16:00 116021
> 2003-JAN-25 17:00 117054
> 2003-JAN-25 18:00 116504
> 2003-JAN-25 19:00 106809
> 2003-JAN-25 20:00 97538
> 2003-JAN-25 21:00 92676
> 2003-JAN-25 22:00 79438
> 2003-JAN-25 23:00 62884
>
> Is there some way I can write a query that will return daily maximums
> for the 'my_value' column? For example query results that I should get
> from the sample data above would be:
> 2003-JAN-24 17:00 147049
> 2003-JAN-25 12:00 120989
>
> This seems like it should be simple but nothing seems to work. Can
> someone please help me?
>
> Thanks,
> Edd Stanley
Received on Wed Jan 29 2003 - 19:57:18 CET

Original text of this message