Menu
How can I request a random row (or as close to truly random as is possible) in pure SQL?
653
Selecting random rows from table in MySQL. Syntax: Here N specifies the number of random rows, you want to fetch. For example: If you want to fetch only 1 random row then you can use the numeric 1 in place N.
Whenever we need to sort a given SQL query result set, we have to use the ORDER BY clause. However, to randomize the returned rows, we need the ORDER BY clause to use a function or database object that returns a random value for each row contained in the SQL result set.
See this post: SQL to Select a random row from a database table. It goes through methods for doing this in MySQL, PostgreSQL, Microsoft SQL Server, IBM DB2 and Oracle (the following is copied from that link):
Select a random row with MySQL:
SELECT column FROM table ORDER BY RAND() LIMIT 1 Select a random row with PostgreSQL:
SELECT column FROM table ORDER BY RANDOM() LIMIT 1 Select a random row with Microsoft SQL Server:
SELECT TOP 1 column FROM table ORDER BY NEWID() Select a random row with IBM DB2
SELECT column, RAND() as IDX FROM table ORDER BY IDX FETCH FIRST 1 ROWS ONLY Select a random record with Oracle:
SELECT column FROM ( SELECT column FROM table ORDER BY dbms_random.value ) WHERE rownum = 1 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
