Question 88941 in PostgreSQL Question posted in
The official documentation can be found here.

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.

Tags:

2

Answers


  1. 0

    Wrap your select into outer one and add where row_sort<=2:

    select id, student, category, score
  from(
select *,
     row_number() over (partition by student order by random()) as row_sort
from my_table) inner_t
where row_sort<=2

    Demo here.

    id student category score
    2 Alex D 5
    2 Alex B 50
    3 Bill A 81
    7 Carl D 10
    7 Carl A 55
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search