Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How Can I Select a Specific Number of Rows Using a Sorting Criterion?
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