Re: Best way to select a random row from a derived table - Mailing list pgsql-hackers

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