Random records in Oracle table based on conditions

Question

I have a Oracle table with the following columns

Table Structure

In a query I need to return all the records with CPER>=40 which is trivial. However, apart from CPER>=40 I need to list 5 random records for each CPID. I have attached a sample list of records. However, in my table I have around 50,000 records. Appreciate if you can help.

Answer 1

Oracle solution:

with CTE as
(
select t1.*, 
       row_number() over(order by DBMS_RANDOM.VALUE) as rn -- random order assigned
from MyTable t1
where CPID <40
)
select *
from CTE
where rn <=5 -- pick 5 at random

union all
select t2.*, null
from my_table t2
where CPID >= 40

SQL Server:

with CTE as
(
select t1.*, 
       row_number() over(order by newid()) as rn -- random order assigned
from MyTable t1
where CPID <40
)
select *
from CTE
where rn <=5 -- pick 5 at random

union all
select t2.*, null
from my_table t2
where CPID >= 40