How can i retrieve (select) half of records from a table, for example, a table with 1000 rows, retrieve 500 (50%) from the table. (in this case i can use rownum because we know the exact quantity of rows (1000) – select * from table where rownum <= 500), but i have to count every table to achieve the statement.
What’s the best way do you think i can do this?
Advertisement
Answer
Well, you could count the rows and select half:
select * from my_table where rownum <= (select count(*)/2 from my_table)
That would tend to select rows that are contiguous within the physical segments.
Or …
select * from (select rownum rn, * from my_table) where mod(rn,2) = 0
That would tend to select “every other” row, so you’d get a pretty even spread from the physical data segments.
Or …
select * from my_table sample (50)
That would be approximately half of the rows.
Or …
select * from my_table sample block (50)
That would be the rows from approximately half of the data blocks below the high water marks of the segments.
Probably lots of different ways available, and which one you want probably depends on whether you want the selected pseudo-randomly or not.
If you want to use the output of the query, use something like:
select ...
from (select *
from my_table
where rownum <= (select count(*)/2 from my_table)) my_table
join ...
In that circumstance the SAMPLE syntax would be more compact.