How to get an random row between a range of rows with LIMIT and OFFSET?

Question

How to get a random row from a selected 50 rows fetched using LIMIT and OFFSET? OFFSET value is dynamic, it can be 0, 50, 100, and so on.

The current solution is to fetch all rows and then pick one random out of them via code, however, would it be more efficient to do it natively using SQL?

SELECT * 
FROM tableName
ORDER BY frequency DESC
LIMIT 50 OFFSET 0

Answer 1

Use your query as a subquery and pick a random row:

SELECT * 
FROM (
  SELECT * FROM tableName
  ORDER BY frequency DESC LIMIT 50 OFFSET 0  
)
ORDER BY RANDOM() LIMIT 1