How to use RANDOM() Function with OFFSET and LIMIT In SQL

In PostgreSQL, the RANDOM() function produces a random number between 0 and 1. Selecting a random selection of rows from a table is the main goal of using RANDOM() with OFFSET and LIMIT in a PostgreSQL query.

Syntax:

SELECT * FROM your_table
OFFSET floor(RANDOM() * (SELECT COUNT(*) FROM your_table))
LIMIT value;

Explanation:

• 1) ‘your_table’: Replace this with the actual name of your table.
• 2) ‘RANDOM()’: This function generates a random value between 0 and 1.
• 3) ‘floor(random() * (SELECT COUNT(*) FROM your_table))’: This section produces a random offset by using floor() to round down after multiplying the number of rows in your table by a random amount. This aids in choosing a random starting place for the table.
• 4) LIMIT value: change the LIMIT value to get the desired amount of random rows.

Example:

SELECT * FROM example_table 
OFFSET floor(random() * (SELECT COUNT(*) 
FROM example_table)) LIMIT 5;

Output:

Explanation: The query mentioned above selects 5 random rows from the example_table, starting from a dynamically determined offset within the table.

In PostgreSQL, selecting a random row from a table can be a useful feature in scenarios such as sampling data for analysis or displaying random content. PostgreSQL provides several techniques to achieve this, including using the RANDOM() function or the ORDER BY RANDOM() clause. In this article, we will explore these methods and demonstrate how to select a random row from a table in PostgreSQL along with examples.