CODEWITHSUNDEEP
sqloracle-database
FirstAider
FirstAider

Reputation: 77

Random records in Oracle table based on conditions

I have a Oracle table with the following columns

Table Structure

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.

Upvotes: 3

Views: 281

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

Assuming that you want five additional random records, you can do:

select t.*
from (select t.*,
             row_number() over (partition by cpid,
                                             (case when cper >= 40 then 1 else 2 end)
                                order by dbms_random.value
                               ) as seqnum
      from t
     ) t
where seqnum <= 5 or cper >= 40;

The row_number() is enumerating the rows for each cpid in two groups -- based on the cper value. The outer where is taking all cper values in the range you want as well as five from the other group.

Upvotes: 0

DxTx
DxTx

Reputation: 3357

How about something like this...

SELECT *
FROM   (SELECT CID,
               CVAL,
               CPID,
               CPER,
               Row_number() OVER (partition BY CPID ORDER BY CPID ASC ) AS RN
        FROM   Table) tmp
WHERE  CPER>=40 OR pids <= 5

However, this is not random.

Upvotes: 0

JohnHC
JohnHC

Reputation: 11205

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

Upvotes: 1

Related Questions