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
Code language: SQL (Structured Query Language) (sql)

Select random rows in Oracle

SELECT column FROM ( SELECT column FROM table ORDER BY dbms_random.value ) WHERE rownum <= 10 [/code] <h2>Select random rows in PostgreSQL</h2> <!-- wp:code {"language": "sql"} --><pre class="wp-block-code"><code></code></pre><!-- /wp:code --> SELECT column FROM table ORDER BY RANDOM() LIMIT 10
Code language: SQL (Structured Query Language) (sql)

Select random rows in Microsoft SQL Server

SELECT TOP 10 column FROM table ORDER BY NEWID()
Code language: SQL (Structured Query Language) (sql)
Also, do comment if you have some variant of the queries described in above article.
Get our Articles via Email. Enter your email address.

You may also like...

11 Comments

  1. Rich LaMarche says:

    Oracle solution should be WHERE rownum <= 10

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

  3. 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

  4. Ilan Hazan says:

    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/

  5. Erasmus Ackon says:

    Thanks a lot. Your solution works as required.

  6. THANKS Rich for updating about the Oracle ………

  7. Sagi Bron says:

    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/

  8. hoan says:

    thank you very much!

  9. ChrisNZak says:

    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;

  10. Krishna says:

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

  11. Hemant Kumar Rout says:

    Hello Sir,Can we store css property into database and retrieve it later?
    How to disable max,min,close,toolbar option for a webpage

Leave a Reply

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