CODEWITHSUNDEEP
sqlpostgresqlquery-performance
Edward Q. Bridges
Edward Q. Bridges

Reputation: 18340

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.

Upvotes: 1

Views: 49

Answers (2)

Mustafa BYKSY
Mustafa BYKSY

Reputation: 95

Distinct on is very good. Also, you can use this query;

WITH year_urls_cte AS (
  SELECT ROW_NUMBER() OVER (
    PARTITION BY year ORDER BY url DESC
  ) AS rn, year,url FROM yearly_urls order by random()
)
SELECT * FROM year_urls_cte
WHERE rn = 1
ORDER BY year;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270421

Use distinct on:

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

Upvotes: 1

Related Questions