Selecting 10 random registers per user

Database
Enthusiast

Selecting 10 random registers per user

Hello!

 

Please, how to select 10 random registers per user on a table?

I am trying to use the over but it is not working ... I must be doing something wrong...

Consider that I want to select all the fields in MY_TABLE, but only 10 random records for each value in the USER field.

Thank you very much =]


Accepted Solutions
Junior Contributor

Re: Selecting 10 random registers per user

You can order the data randomly and apply a Rosw_Number:

select ...
from
 (
select t.*, random(-1000000,1000000) as rnd from mytable ) as dt qualify row_number() over (partition by user_name order by rnd) <= 10

You must use Derived Table (or CTE) because Random is not allowed in (Windowed) Aggregates 

 

1 ACCEPTED SOLUTION
2 REPLIES
Junior Contributor

Re: Selecting 10 random registers per user

You can order the data randomly and apply a Rosw_Number:

select ...
from
 (
select t.*, random(-1000000,1000000) as rnd from mytable ) as dt qualify row_number() over (partition by user_name order by rnd) <= 10

You must use Derived Table (or CTE) because Random is not allowed in (Windowed) Aggregates 

 

Enthusiast

Re: Selecting 10 random registers per user

Thank you, Dieter! ;)