Re: Obtains rows 10-20 in a SELECT with ORDER BY!
Date: 1998/05/25
Message-ID: <3569906f.32544712_at_www.sigov.si>#1/1
[Quoted] 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.
[Quoted] [Quoted] 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 CEST