CODEWITHSUNDEEP
sqlpostgresqlactiverecord
everett1992
everett1992

Reputation: 2671

How to randomize the order of DISTINCT ON results?

I have a table of podcast episodes 

id | title | podcast_id | publication_date
 1 |   "a" |          1 |            today
 2 |   "b" |          2 |            today
 3 |   "c" |          1 |            yesterday
 4 |   "d" |          2 |            yesterday

With this query I can the latest episode from each podcast.

SELECT DISTINCT ON (episodes.podcast_id) episodes.* FROM "episodes"
WHERE ...
ORDER BY episodes.podcast_id, "episodes"."publication_date" DESC

Results in

id | title | podcast_id | publication_date
 1 |   "a" |          1 |            today
 2 |   "b" |          2 |            today

I'd like to randomize the order of the podcasts returned, so that podcast_id 2 might be returned before 1, but adding RANDOM() to the final ORDER BY clause changes which episode for the given podcast is chosen.

To summarize: I want the newest episodes from each podcast in a random order.

Edit: I should mention that this query is the result of chaining many ActiveRecord scopes together. Here is the final answer translated to ActiveRecord

# This ActiveRecord statement is equivalent to the above SQL query
episodes = Episode
  .unscoped
  .select("DISTINCT ON (episodes.podcast_id) episodes.*")
  .order("episodes.podcast_id, episodes.publication_date")

Episode
  .unscoped
  .from("(#{sql.to_sql}) episodes")
  .order("random()")

I'm not sure if this is a good way to do it. The advantages as I see them are

  1. Single query
  2. Result is ActiveRecord_Relation, you can continue to chain limit or pluck

Upvotes: 2

Views: 64

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125424

select *
from (
    SELECT DISTINCT ON (episodes.podcast_id) episodes.*
    FROM "episodes"
    WHERE ...
    ORDER BY episodes.podcast_id, "episodes"."publication_date" DESC
) s
order by random()

Upvotes: 3

Related Questions