Randomly pick one distinct row of every group?

Question

Let say I have this SQL in Oracle :

 select field1,field2 from table where field2 in (32,421,5766) and .....

it normally will return many rows. How do I pick 3 random rows ONE from every group that is defined by field2 and also match the rest of the conditions

Answer 1

Use the ROW_NUMBER analytic function and give the rows a random order:

SELECT field1,
       field2
FROM   (
  SELECT field1,
         field2,
         ROW_NUMBER() OVER ( PARTITION BY field2 ORDER BY DBMS_RANDOM.VALUE() )
           AS rn
  FROM   table_name
  WHERE  field2 IN (32, 421, 5766)
)
WHERE  rn = 1;

Which, for the sample data:

CREATE TABLE table_name ( field1, field2 ) AS
SELECT LEVEL,    1 FROM DUAL CONNECT BY LEVEL <= 10 UNION ALL
SELECT LEVEL,   32 FROM DUAL CONNECT BY LEVEL <= 10 UNION ALL
SELECT LEVEL,  421 FROM DUAL CONNECT BY LEVEL <= 10 UNION ALL
SELECT LEVEL, 5766 FROM DUAL CONNECT BY LEVEL <= 10;

May (randomly) output:

FIELD1 | FIELD2
-----: | -----:
     3 |     32
     6 |    421
     7 |   5766

db<>fiddle here