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

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 ;
>
> 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?

Oracle version and OS?

First, is that second insert a typo? "insert into temp_table_2 select ...from temp_table_2" Given the overall context, I'd bet you really meant "insert into temp_table_2 select ...from temp_table_1"

Second, assuming the above, I'm not sure what you think you're supposed to get from this. On your first insert, into temp_table_1, even though you have an ORDER BY in your subselect, there can be no presumption of that having any meaning once the rows are actually inserted into the table. Remember, in a relational DB, there is no implied or promised ordering of rows in a table. Ordering is ONLY a product of an ORDER BY in a SELECT statement, at the time that statement is executed.

With that in mind ...

Third, the rownum pseudocolumn is assigned to rows *in the result set* .... NOT on the table itself.

The result of all of the above is that your statements do this:

> insert into temp_table_1 select id_number, criterion from source_view
> order by criterion ;

[ select all of the rows from source_view, sort on criterion, then insert into temp_table_1. When this is completed, the idea that the rows are ordered on the table has no meaning, so the resources spent on the ORDER BY was wasted.]

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

[select all of the rows from temp_table_?. They will be returned in no guaranteed order, so must be assumed to be random; take the first 5000 of these random rows and insert them into temp_table_2. Notice also that since the result set from which the 5000 rows are selected is assumed to be randomly ordered, the concept of the 'first' 5000 is somewhat meaningless as well. The 'first' n rows from a random set could just as well be 'any' n rows from the random set.]

Maybe we should step back and look at the end result you are trying to achieve instead of the technique you are trying to use to get there ... Received on Wed Jun 21 2006 - 13:39:13 CDT