Fetch Random rows from Database (MySQL, Oracle, MS SQL, PostgreSQL)
- By Viral Patel on May 18, 2009
Ever thought why would one need to fetch random rows from a database table? You may want to display some random information (like articles/pages/links) to your user.
I had a requirement wherein I had to fetch 10 random rows from MySQL database and display it on screen. I used following simple query for retrieving random records from database table.
Also note that there are number of ways one can fetch random rows from table. Easiest way is to use sql queries to do so. Now there are some different queries depending on your database server.
Following are the examples of fetching random rows in some popular databases.
Select random rows in MySQL
Following query will fetch 10 random rows from MySQL.
SELECT column FROM table ORDER BY RAND() LIMIT 10
Select random rows in Oracle
SELECT column FROM ( SELECT column FROM table ORDER BY dbms_random.value ) WHERE rownum <= 10
Select random rows in PostgreSQL
SELECT column FROM table ORDER BY RANDOM() LIMIT 10
Select random rows in Microsoft SQL Server
SELECT TOP 10 column FROM table ORDER BY NEWID()
Also, do comment if you have some variant of the queries described in above article.
Get our Articles via Email. Enter your email address.