Re: Best way to select a random row from a derived table

Condor <condor@xxxxxxxxxx> · Sun, 28 Jan 2018 12:09:35 +0200

On 28-01-2018 08:39, Ryan Murphy wrote:
Hello hackers and postgressers,

I am aware of 2 ways to select a random row from a table:

    1) select * from table_name order by random() limit 1;
        -- terribly inefficient

    2) select * from table_name tablesample system_rows(1) limit 1;
        -- only works on tables, not views or subqueries

Is there an option that is reasonably efficient and can be used on
views and subqueries?

Thanks!
Ryan

I do it with:

SELECT * FROM table_name OFFSET RANDOM() * LIMIT 1;

Regards,
HC