Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: strange behaviour (query) ...
SELECT MAX(a)
FROM banane MINUS ( SELECT MAX(a) AS x FROM banane );
will not work 'cos the result sets from the 2 selects are the same,ie 5 in
both cases using your example data. You can do it with just one subselect
as
select max(a)
from banane
where a < ( select max(a) from banane);
but I wouldn't like to run such a query on any high volume tables.
Rob
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
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 Received on Mon Feb 24 1997 - 00:00:00 CST