Search code examples
sqlpostgresqlquery-performance

Selecting a random element in a category

Given a table in a PostgreSQL 11 database that resembles this:

CREATE TABLE yearly_urls ( year int, url varchar );

That has many thousands of rows of URLs over several years like this:

┌──────┬───────────┐
│ year | url       │
├──────┼───────────┤
│ 2009 │ /abc.jpeg │
│ 2009 │ /def.jpeg │
│ 2017 │ /ghi.jpeg │
│ 2018 │ /jkm.jpeg │
│ 2018 │ /nop.jpeg │
└──────┴───────────┘

What's the best way to write a query to retrieve a random URL for a given year?

In other words, one URL chosen at random for a given year.

Solution

  • Use distinct on:

    select distinct on (year) year, url
from t
order by year, random();