0 votes
1 view
in SQL by (20.3k points)

How can I request a random row (or as close to truly random as is possible) in pure SQL?

1 Answer

0 votes
by (40.3k points)

There are many ways to select a random tuple/ row from a database table.

Here, I have listed a few examples of SQL statements that don't require additional application logic.

For the different database server, you require different SQL syntax.

     1. To select a random row in MySQL, use this SQL Syntax:

 SELECT column FROM Table 

ORDER BY RAND()

LIMIT 1

 2. To select a random row in PostgreSQL, use this SQL Syntax:

SELECT column FROM Table

ORDER BY RANDOM()

LIMIT 1

 3. To select a random row in Microsoft SQL Server, use this SQL Syntax:

SELECT TOP 1 column FROM Table

ORDER BY NEWID()

  4. To select a random row in IBM DB2, use this SQL Syntax:

SELECT column, RAND() as IDX

FROM Table

ORDER BY IDX FETCH FIRST 1 ROWS ONLY

 5. To select a random record in Oracle, use this SQL Syntax:

SELECT column FROM

(SELECT column FROM Table1

ORDER BY dbms_random.value)

WHERE row_num = 1

Related questions

0 votes
1 answer
asked Jul 10, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...