[PostgreSQL] ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

Question

I am running the following queries to randomly select 5 persons from the table, but it gives an error - "ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list". How to fix the error?

select distinct person_id 

from customers 

order by random() 

limit 5;

Answer 1

You can change the SQL query as follow to randomly select 5 persons from the table:

select distinct person_id 

from (select person_id from customers order by random()) foo 

limit 5;

You need to select without the distinct clause, randomize the results, and then use select with the distinct clause. The above SQL will not give the error.