lundi 26 octobre 2020

Select one random row by group (Oracle 10g)

This post is similar to this thread in that I have multiple observations per group. However, I want to randomly select only one of them. I am also working on Oracle 10g.

There are multiple rows per person_id in table df. I want to order each group of person_ids by dbms_random.value() and select the first observation from each group. To do so, I tried:

select
    person_id, purchase_date
from
    df
where
    row_number() over (partition by person_id order by dbms_random.value()) = 1

The query returns:

ORA-30483: window functions are not allowed here 30483. 00000 - "window functions are not allowed here" *Cause: Window functions are allowed only in the SELECT list of a query. And, window function cannot be an argument to another window or group function.




Aucun commentaire:

Enregistrer un commentaire