RANDOM Row fetch from a database table

How to random Row fetch from a database table in SQL, MySQL, PostgreSQL, DB2, SQL Server.

SQL RAND function generate random number between 0 to 1. But no longer support RAND() function for generate random raw from database table.

But following way you can fetch random record from the database.

Select random record in Oracle SQL 10g/11g/12c

Syntax

SELECT column FROM
    ( SELECT column FROM table
    ORDER BY dbms_random.value )
    WHERE rownum = 1;

Example

SQL> SELECT * FROM 
  ( SELECT * FROM product 
   ORDER BY dbms_random.value) 
WHERE rownum = 1;

CATEGORY_ID PRODUCT_NAME
----------- --------------------
          1 Samsung

Run it...   »


Select random record in MySQL

Syntax

SELECT column FROM table
    ORDER BY RAND()
    LIMIT 1;

Example

SQL> SELECT * FROM product
    ORDER BY RAND()
    LIMIT 1;

CATEGORY_ID PRODUCT_NAME
----------- --------------------
          3 Apple

Run it...   »

Select random record in PostgreSQL

Syntax

SELECT column FROM table
    ORDER BY RANDOM()
    LIMIT 1;

Example

SQL> SELECT * FROM product
    ORDER BY RANDOM()
    LIMIT 1;

CATEGORY_ID PRODUCT_NAME
----------- --------------------
          2 HP

Run it...   »


Select random record in Microsoft SQL Server

Syntax

SELECT TOP 1 column 
    FROM table
    ORDER BY NEWID();

Example

SQL> SELECT TOP 1 * 
    FROM product
    ORDER BY NEWID();

CATEGORY_ID PRODUCT_NAME
----------- --------------------
          3 Apple

Run it...   »


Select random record in SQLite

Syntax

SELECT * FROM table_name
    ORDER BY RANDOM() 
    LIMIT 1;

Select random record in IBM DB2

Syntax

SELECT column, RAND() as RECORD
    FROM table 
    ORDER BY RECORD FETCH FIRST 1 ROWS ONLY;