Choose a random row for each group in the postgres table

Question

I have a table about:

id | category | link | caption | image 

My goal is to get a random row from each individual category in the table for all categories in the table. The plan is then to assign each row a variable for the corresponding category.

I am currently using several SELECT statements similar to:

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

But it seems inelegant and creates more trips to the database, which is expensive.

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

Thanks for any help!

Answer 1

Try something like:

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

Or using the window functions:

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