Home » SQL & PL/SQL » SQL & PL/SQL » Help with max and min query
Help with max and min query [message #272347] Thu, 04 October 2007 11:49 Go to next message
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 #272351 is a reply to message #272347] Thu, 04 October 2007 12:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> with 
  2    data as (
  3      select to_date('01/01/2007','DD/MM/YYYY')+(level-1)/24 date_time,
  4             level-1 value
  5      from dual
  6      connect by level <= 7
  7    )
  8  select date_time, value
  9  from ( select date_time, value,
 10                row_number () over (order by value) rn1,
 11                row_number () over (order by value desc) rn2
 12         from data )
 13  where rn1 = 1 or rn2 = 1
 14  /
DATE_TIME                VALUE
------------------- ----------
01/01/2007 06:00:00          6
01/01/2007 00:00:00          0

2 rows selected.

Regards
Michel
Re: Help with max and min query [message #272354 is a reply to message #272351] Thu, 04 October 2007 12:47 Go to previous messageGo to next message
bztom33
Messages: 95
Registered: June 2005
Member

What if I want to get the max and min value with a restricted date_time parameter.

date_time > '01/01/2007 00:00' and
date_time <= '10/02/2007 23:00' and
sales_id = 1234


[Updated on: Thu, 04 October 2007 12:47]

Report message to a moderator

Re: Help with max and min query [message #272355 is a reply to message #272347] Thu, 04 October 2007 12:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>'01/01/2007 00:00' and '10/02/2007 23:00' are STRINGS not date datatypes; just like 'I am a lazy coder' is a string.

When you want a DATE data type use the TO_DATE function!
Re: Help with max and min query [message #272357 is a reply to message #272354] Thu, 04 October 2007 12:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I gave you the start, now work a little bit and post what you tried.

Regards
Michel
Re: Help with max and min query [message #272367 is a reply to message #272357] Thu, 04 October 2007 13:58 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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

Re: Help with max and min query [message #272372 is a reply to message #272369] Thu, 04 October 2007 15:16 Go to previous messageGo to next message
bztom33
Messages: 95
Registered: June 2005
Member

Problem solved.
Re: Help with max and min query [message #272423 is a reply to message #272372] Fri, 05 October 2007 00:51 Go to previous message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
So post your solution.
Don't you think it would useful for others?

Regards
Michel
Previous Topic: How to get count of substition variables in SQLPLUS
Next Topic: How to avoid parallel updates on a table in a procedure.
Goto Forum:
  


Current Time: Sun Nov 10 05:15:39 CST 2024