Select random row for each group in a postgres table

Question

I have a table that is roughly:

id | category | link | caption | image

My goal is to fetch a random row from each distinct category in the table, for all the categories in the table. The plan is to then assign each row to a variable for its respective category.

Right now I'm using multiple SELECT statements resembling:

SELECT link, caption, image FROM table
       WHERE category='whatever'
       ORDER BY RANDOM() LIMIT 1`

But this seems inelegant and creates more trips to the DB, which is expensive.

I'm pretty sure there's a way to do this with window functions in Postgres, but I have no experience with them and I'm not entirely sure how to use one to get what I want.

Ihor Romanchenko · Accepted Answer · 2013-08-30 18:28:31Z

29

Try something like:

SELECT DISTINCT ON (category) *
FROM table 
ORDER BY category, random();

Or with window functions:

SELECT * 
FROM (
SELECT *, row_number() OVER (PARTITION BY category ORDER BY random()) as rn
FROM table ) sub
WHERE rn = 1;

answered Aug 30, 2013 at 18:28

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Steve Over a year ago

Excellent! Both of those seem to work for my purposes. Thank you!

Collectives™ on Stack Overflow

Select random row for each group in a postgres table

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related