Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select random row for each group in a postgres table

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.

Thanks for any help!

like image 574
Steve Avatar asked Aug 30 '13 18:08

Steve


People also ask

How do I select a random row in PostgreSQL?

There's an easy way to show a random record in a table: SELECT * FROM table_name ORDER BY RANDOM() LIMIT 1; But this query might take a while to finish as it reads the whole table first then take out a random record from it.

How do I randomly select rows from a table?

To get a single row randomly, we can use the LIMIT Clause and set to only one row. ORDER BY clause in the query is used to order the row(s) randomly. It is exactly the same as MYSQL. Just replace RAND( ) with RANDOM( ).

What does group by do in PostgreSQL?

The PostgreSQL GROUP BY clause is used to divide rows returned by SELECT statement into different groups. The speciality of GROUP BY clause is that one can use Functions like SUM() to calculate the sum of items or COUNT() to get the total number of items in the groups.


1 Answers

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;
like image 73
Ihor Romanchenko Avatar answered Sep 20 '22 14:09

Ihor Romanchenko