Re: How Can I Select a Specific Number of Rows Using a Sorting Criterion?

From: <fitzjarrell_at_cox.net>
Date: 21 Jun 2006 11:20:32 -0700
Message-ID: <1150914032.103285.297570@u72g2000cwu.googlegroups.com>

Comments embedded.
Helge wrote:
> I'd like to do something like this:
>
> insert into temp_table_1 select id_number, criterion from source_view
> order by criterion ;
> insert into temp_table_2 select id_number, criterion from temp_table_2
> where rownum <= 5000 ;
>

Which, of course, could be a very fast insert indeed if no rows exist in temp_table_2 prior to this attempted insert. If you actually intended this:

 insert into temp_table_2 select id_number, criterion from temp_table_1  where rownum <= 5000 ;

that creates a different issue.

> Of course, the order by clause isn't allowed in a subquery.
>

Where is the subquery? And where did you find this 'nugget of wisdom'?  It certainly doesn't apply to 9iR2 or later releases of Oracle:

SQL> create table emp2 as select * from emp where 0=1;

Table created.

SQL> insert into emp2
  2 select * from (select * from emp order by ename) where rownum <=5;

5 rows created.

SQL> select *
  2 from emp2;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL
COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7876 ADAMS      CLERK           7788 23-MAY-87       1100
           20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600
300         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
           30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450
           10
      7902 FORD       ANALYST         7566 03-DEC-81       3000
           20

SQL> As such your amended statement would be:

 insert into temp_table_2 select id_number, criterion from (select id_number, criterion from temp_table_1 order by id_number) where rownum <= 5000 ;

> Instead I've come up with a brute force method, which is to mark all
> rows that I want to include, count the marked rows, and if they aren't
> enough increase the selection criterion by one step and mark the rows
> again. Repeat until the count is large enough. It's not very elegant,
> but it's the only method I could think of.
>
> Does anyone else have a better way to do this?

Yes, and it's posted above.

David Fitzjarrell Received on Wed Jun 21 2006 - 13:20:32 CDT