Postgres group by and then show one random from that group

Question

I have a table of records each of which belongs to a collection. I want to list the collections and with each collection have a random record from that collection.

Currently I have a count but I would like to also return the random id from the records table. I cannot get my head around it. Any suggestions?

Current query.

 select
   collections.name,collections.id, count(records.id) as count
 from
   collections, records
 where
   records.collection_id = collections.id
 group by
   collections.id
 order by
   collections.name

possible duplicate of http://stackoverflow.com/questions/5297396/quick-random-row-selection-in-postgres — awsome, May 12 '16 at 14:04
Not sure this is a duplicate as the link doesn't include a join that I can see. — latitudehopper, May 12 '16 at 14:07
following the above question, yours should be something like select collections.id AS random.id from collections, records where records.collection_id = collections.id group by collections.id OFFSET floor(random()* (select count(*) from collections, records where records.collection_id = collections.id group by collections.id )) LIMIT 1; — awsome, May 12 '16 at 14:18
`ERROR: more than one row returned by a subquery used as an expression` — latitudehopper, May 12 '16 at 14:20
remove group by "select collections.id AS random.id from collections, records where records.collection_id = collections.id OFFSET floor(random()* (select count(*) from collections, records where records.collection_id = collections.id )) LIMIT 1;" — awsome, May 12 '16 at 14:24

score 0 · Answer 1 · answered May 17 '16 at 08:55

Adding as an answer, from the comments above

select collections.id AS random.id from collections, records 
 where records.collection_id = collections.id 
  OFFSET floor(random()* (select count(*) from collections, records 
                          where records.collection_id = collections.id ))
  LIMIT 1;

Postgres group by and then show one random from that group

1 Answers1