How to retrieve half of records from a table – Oracle 11g

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?

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.