Re: Question about finding maximum and minimum

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 07 Dec 1998 23:00:55 GMT
Message-ID: <36705d95.15572942@192.86.155.100>

A copy of this was sent to thtsang_at_my-dejanews.com (if that email address didn't require changing) On Mon, 07 Dec 1998 06:27:27 GMT, you wrote:

>Hello,
>
>I have a silly question about SQL statements. I have the following SQL
>statement:
>
>select t1.a
>from t t1
>where t1.a >= a1 and
> t1.a <= a2
>
>where a1, a2 are 2 variables that are defined somewhere else, so that they
>cannot be used anywhere else in the SQL statement.
>
>However, I only need the maximum and minimum of the results. That is, the
>following SQL statement will suit my need:
>
>select max(t1.a), min(t1.a)
>from t t1
>where t1.a >= a1 and
> t1.a <= a2
>
>The problem is that I cannot change anything in the original SQL statement (I
>know this is silly but I am under such restriction.) What I can do is only
>adding something at the end. It can involve a sub-query. Also, what makes the
>problem challenging is that I cannot use a1 and a2 in the thing I add to the
>end.
>

silly, its not productive.

anyway:

select t1.a
  from t t1

 where t1.a >= a1 and
       t1.a <= a2
   and t1.a in ( select min(t1.a) 
                   from t1 where t1.a between a1 and a2 
                 union all 
                 select max(t1.a)
                   from t1 where t1.a between a1 and a2)

the subquery is realy your answer -- the subquery answers your original question.

this query will return 0, 1 or 2 rows...

>Please tell me how to do this, or whether it is impossible to do this in one
>SQL statement.
>
>Thank you very much
>
>Anthony
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
\  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Dec 07 1998 - 17:00:55 CST