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

dice-illusionEver 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.



10 Comments

  • Rich LaMarche 18 May, 2009, 18:57

    Oracle solution should be WHERE rownum <= 10

  • Viral Patel 18 May, 2009, 19:06

    Good Catch Rich..
    Thanks for pointing out the typo.. I updated the query.

  • Justin Swanhart 23 May, 2009, 16:05

    That is a really bad way to get random rows from a table. It always requires a full table scan.

    This guy’s blog has some alternate ideas for MySQL:
    http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-table/
    and here:
    http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/#comment-2838

  • Ilan Hazan 1 December, 2009, 1:43

    Order by rand has bad performance with big tables.
    There is another way to produce random rows using only a query and without order by rand().
    It involves User Defined Variables.
    See http://www.mysqldiary.com/how-to-produce-random-rows-from-a-table/

  • Erasmus Ackon 26 January, 2010, 0:34

    Thanks a lot. Your solution works as required.

  • Design web london 5 July, 2010, 20:01

    THANKS Rich for updating about the Oracle ………

  • Sagi Bron 25 January, 2011, 18:47

    In order to find random rows from a table, don’t use ORDER BY RAND() because it forces MySQL to do a full file sort and only then to retrieve the limit rows number required. In order to avoid this full file sort, use the RAND() function only at the where clause. It will stop as soon as it reaches to the required number of rows.
    See
    http://www.rndblog.com/how-to-select-random-rows-in-mysql/

  • hoan 27 October, 2012, 13:38

    thank you very much!

  • ChrisNZak 2 May, 2013, 1:38

    below query selects only two records from the list of agents..i.e 2 random records for each agent over the span of a week etc…. it works !!

    with summary as ( Select Dbms_Random.Random As Ran_Number, colmn1, colm2, colm3 Row_Number() Over(Partition By col2 Order By Dbms_Random.Random) As Rank From table1, table2 Where Table1.Id = Table2.Id Order By Dbms_Random.Random Asc) Select tab1.col2, tab1.col4, tab1.col5, From Summary s Where s.Rank <= 2;

  • Krishna 7 August, 2013, 2:28

    Can any one tell me the SQL Server Query to retrieve random records from more than one table(2 or more).

Leave a Reply

Your email address will not be published. Required fields are marked *

Note

To post source code in comment, use [code language] [/code] tag, for example:

  • [code java] Java source code here [/code]
  • [code html] HTML here [/code]

Current ye@r *