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

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/05/25
Message-ID: <3569906f.32544712@www.sigov.si>#1/1

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

SELECT <bottom_20_records> FROM my_table MINUS
SELECT <bottom_10_records> FROM my_table.

Here is an example from SCOTT.EMP, ranking on SAL:

SQL> select empno, ename, sal from emp order by sal;

    EMPNO ENAME SAL
--------- ---------- ---------

     7369 SMITH            800
     7900 JAMES            950
     7876 ADAMS           1100
     7521 WARD            1250
     7654 MARTIN          1250
     7934 MILLER          1300
     7844 TURNER          1500
     7499 ALLEN           1600
     7782 CLARK           2450
     7698 BLAKE           2850
     7566 JONES           2975
     7788 SCOTT           3000
     7902 FORD            3000
     7839 KING            5000

14 rows selected.

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;

   ROWNUM EMPNO ENAME SAL --------- --------- ---------- ---------

        1      7369 SMITH            800
        2      7900 JAMES            950
        3      7876 ADAMS           1100
        4      7521 WARD            1250
        5      7654 MARTIN          1250
        6      7934 MILLER          1300
        7      7844 TURNER          1500
        8      7499 ALLEN           1600
        9      7782 CLARK           2450
       10      7698 BLAKE           2850

10 rows selected.

And the final sollution to your problem, to display only employees, ranking from 6 to 10, use the following:

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 MINUS
  5 select rownum, a.empno, a.ename, a.sal from emp a, dual b   6 where a.sal = decode(b.dummy(+),'X',NULL,NULL)   7 and rownum <= 5
  8 order by 4;

   ROWNUM EMPNO ENAME SAL --------- --------- ---------- ---------

        6      7934 MILLER          1300
        7      7844 TURNER          1500
        8      7499 ALLEN           1600
        9      7782 CLARK           2450
       10      7698 BLAKE           2850

SQL> Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Mon May 25 1998 - 00:00:00 CDT