mercredi 19 avril 2023

PostgreSQL: How to select N random entries per column value

I have the following code

with my_table (id, student, category, score)
as (values 
(1, 'Alex', 'A', 11),
(2, 'Alex', 'D', 4),
(2, 'Alex', 'B', 50),
(2, 'Alex', 'C', 83),
(2, 'Alex', 'D', 5),
(3, 'Bill', 'A', 81),
(6, 'Carl', 'C', 5),
(7, 'Carl', 'D', 2),
(7, 'Carl', 'B', 21),
(7, 'Carl', 'A', 55),
(7, 'Carl', 'A', 86),
(7, 'Carl', 'D', 10)
)


select *,
     row_number() over (partition by student order by random()) as row_sort
from my_table

I would like to know how I can adjust it to return 2 random entries per student.




Aucun commentaire:

Enregistrer un commentaire