Re: strange behaviour (query) ...

Michael Agbaglo wrote:
>
> SQL*Plus: Release 3.2.2.0.1 - Production on Mon Feb 24 21:34:09 1997
>
> I want to determine the 2nd greatest number. Why does
>
> SELECT MAX(a)
> FROM banane MINUS ( SELECT MAX(a) AS x FROM banane );
>
> not work ? Why are 3 SELECTs necessary ?
 

This does not work because both selects return the same result set.

   b - b = 0

> (Personal Oracle7 Release 7.2.2.3.1)
>
> SQL> SELECT * FROM banane;
>
> A
> ---------
> 1
> 2
> 3
> 5
>
> SQL> SELECT MAX(a)
> 2 FROM banane MINUS ( SELECT MAX(a) AS x FROM banane );
>
> no rows selected
>
> SQL> SELECT MAX(a)
> 2 FROM ( SELECT * FROM banane MINUS ( SELECT MAX(a) FROM banane));
>
> MAX(A)
> ---------
> 3

Give this a try:

   select max(a)
     from junk
    where a < (select max(a) from junk)

-- 
Thomas Griffin
Project Leader
QUALITECH Systems, Inc.
tgriffin_at_qualitech.com