Dennis Cote wrote:
To get every N'th row after deletions you need some way to assign a
series of integers to the result rows. The easiest way I can think of
is to create a temporary table from your initial query. Then you can
use the modulus operator to select every N'th record from that table
as you have suggested since the rowids will all be freshly assigned.
You will also need to drop the temp table when you are done with it.
create temp table temp_table as select * from my_table where ....;
select * from temp_table where rowid % N = 0;
drop table temp_table;
If the table rows are large, or if the number of rows is large, you
might want to do this refinement:
create temp_table as select rowid as source_rowid from my_table WHERE ...;
select * from my_table, temp_table where temp_table.rowid%N=0 and
source_rowid=my_table.rowid;
drop table temp_table;
Actually, this looks like a great way to implement many kinds of weird
sorting/indexing schemes (percentile ranking, hi/low ordering, grouping).
Such a case would be to find the decile rankings of an table (with
numbers in it). In that case, N would be the count(*) / 10. and the
original WHERE would describe the order over which the ranking is to be
done. Or use count/2 to get at the median. (if N < 100, one might also
need to interpolate).
