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 ?
>
> (Personal Oracle7 Release 7.2.2.3.1)
>
> SQL> SELECT * FROM banane;
>
> A
> ---------
> 1
> 2
> 3
> 5

This query is returning the difference between two equivalent sets resulting in no rows.

>
> 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

How about:

SELECT MAX(a)
 FROM banane where a < (SELECT MAX(a) FROM banane);

         James H. Yoshii
 IntelliTech Business Solutions

        yoshii_at_planet.net