Introduction SQL

Basic SQL

SQL Constraints

Advance SQL

SQL Joins

SQL Function


RANDOM Row fetch from a database table in SQL, MySQL, PostgreSQL, DB2, SQL Server


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;