14

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.

Alexis Wilke
  • 19,179
  • 10
  • 84
  • 156
Steve
  • 325
  • 5
  • 13

1 Answers1

28

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;
Ihor Romanchenko
  • 26,995
  • 8
  • 48
  • 44