Fetch Random rows from Database (MySQL, Oracle, MS SQL, PostgreSQL)

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 Microsoft SQL Server

SELECT TOP 10 column FROM table
ORDER BY NEWID()

Select random rows in Oracle

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum <= 10
[/code]

Select random rows in PostgreSQL


SELECT column FROM table ORDER BY RANDOM() LIMIT 10