Help with max and min query [message #272347] |
Thu, 04 October 2007 11:49 |
bztom33
Messages: 95 Registered: June 2005
|
Member |
|
|
Hi,
How would I write a query that return max and min values for the following output?
max = 01/01/2007 06:00 6
min = 01/01/2007 00:00 0
table layout:
date_time value
01/01/2007 00:00 0 <------min value with date_time
01/01/2007 01:00 1
01/01/2007 02:00 2
01/01/2007 03:00 3
01/01/2007 04:00 4
01/01/2007 05:00 5
01/01/2007 06:00 6 <-------max value with date_time
Thanks
Tom
[Updated on: Thu, 04 October 2007 11:49] Report message to a moderator
|
|
|
|
|
|
|
Re: Help with max and min query [message #272367 is a reply to message #272357] |
Thu, 04 October 2007 13:58 |
bztom33
Messages: 95 Registered: June 2005
|
Member |
|
|
There's not such command as WITH. I am using Oracle 8i.
1 with
2 data as(
3 select to_date(date_time,'MM/DD/YYYY HH24:MI') +
(level -1)/24 date_time, level-1 value from test x where
4 date_time >='10/01/2007 08:00'
and date_time <='10/04/2007 08:00'
connect by level <=23
5 )
6 select date_time,value
7 from (select date_time, value
8 row_number() over (order by value) rn1,
9 row_number() over (order by value desc) rn2
10 from data)
11* where rn1 =1 or rn2 =1
SQL> /
with
*
ERROR at line 1:
ORA-00900: invalid SQL statement
[Updated on: Thu, 04 October 2007 14:04] Report message to a moderator
|
|
|
Re: Help with max and min query [message #272368 is a reply to message #272347] |
Thu, 04 October 2007 14:02 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
>There's not such command as WITH. I am using Oracle 8i.
Upgrade to a version that has been supported during this century.
This is a prime example why you should have provided Oracle version (to 4 decimal places) in FIRST post.
[Updated on: Thu, 04 October 2007 14:02] by Moderator Report message to a moderator
|
|
|
Re: Help with max and min query [message #272369 is a reply to message #272367] |
Thu, 04 October 2007 14:03 |
|
Michel Cadot
Messages: 68711 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
This is why you have to provide the version in your FIRST post.
Now I encourage you to read 9i and up documentation to know what is WITH and to find an equivalent in 8i. This will be a good exercise.
In addition, you don't need this part of the query if you have the table you said you have.
Regards
Michel
[Updated on: Thu, 04 October 2007 14:04] Report message to a moderator
|
|
|
|
|