Re: Obtains rows 10-20 in a SELECT with ORDER BY!

On Mon, 25 May 1998 15:51:05 GMT, jurij.modic_at_mf.sigov.mail.si (Jurij Modic) wrote:

>On Mon, 25 May 1998 13:42:25 +0100, Fidel Cacheda Seijo
><fidel_at_cesat.es> wrote:
>
>> The problem is easy: in a SELECT that returns 100 rows (for example), I
>>just need rows from 10 to 20. This is easy to do, but the problem is
>>that I need that the query performs first the ORDER BY, and then obtain
>>the rows I want.
>>
>> Any idea??
>
>If you know how to select top_N/bottom_N of ordered records (there are
>a couple of ways to do this without PL/SQL), then you can use MINUS
>operator on two different queies to suppres the return of first M
>records, e.g (pseudocode):

[...]

>To return bottom_10 records (based on sal), you can use the following
>query (extremely fast even on large tables):
>
>SQL> select rownum, a.empno, a.ename, a.sal from emp a, dual b
> 2 where a.sal = decode(b.dummy(+),'X',NULL,NULL)
> 3 and rownum <= 10
> 4 order by 4;

[...]

Well, that's great !

Thank you very much Jurij for this most interesting posting. I wasn't aware of this way of solving the problem.

But I'm totally in the dark why and how this is working. According to what the docs say about the rownum pseudocolumn, it should not work this way.

Could someone shed some light on this ?
Is this specific to an Oracle release, or to an optimizer mode ?

Or can one expect that this is portable and guaranteed to always work this way ?

TIA,
Peter

-- 
Peter Schneider
peter.schneider_at_okay.net