PostgreSQL: How to select N random entries per column value

Question

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.

Answer 1

value of window function can only be accessed after thwe query has run, so using a second CTE will do the trick

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)
),
CTE2 as (select *,
     row_number() over (partition by student order by random()) as row_sort
from my_table)
SELECT id, student, category, score
  FROM CTE2
WHERE row_sort <= 2

id	student	category	score
2	Alex	C	83
2	Alex	D	5
3	Bill	A	81
7	Carl	A	86
7	Carl	B	21

SELECT 5

fiddle