Return two random rows without duplicate properties in PostgreSQL

Question

Let's say I have a table of customer addresses:

Name           |   AddressLine
-------------------------------
John Smith      | 123 Nowheresville
Jane Doe        | 456 Evergreen Terrace
John Smith      | 999 Somewhereelse
Joe Bloggs      | 1 Second Ave

I would like to return two random rows from this table, but I do not want to return two rows with the same Name (example of what I don't want):

Name           |   AddressLine
-------------------------------
John Smith      | 123 Nowheresville
John Smith      | 999 Somewhereelse

How can I do this in Postgres?

Gordon Linoff · Accepted Answer · 2017-06-02 18:18:36Z

3

Here is one method:

select distinct on (name) t.*
from t
order by name, random();

answered Jun 2, 2017 at 18:18

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Luke Schlangen Over a year ago

Will that ever return the second row for John Smith? 999 Somewhereelse?

Gordon Linoff Over a year ago

@LukeSchlangen . . . It could. random() is random.

Collectives™ on Stack Overflow

Return two random rows without duplicate properties in PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related