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

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 ;

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

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? Received on Wed Jun 21 2006 - 12:12:48 CDT