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.
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/
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;
Oracle solution should be WHERE rownum <= 10
Good Catch Rich..
Thanks for pointing out the typo.. I updated the query.
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
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/
Thanks a lot. Your solution works as required.
THANKS Rich for updating about the Oracle ………
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/
thank you very much!
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;
Can any one tell me the SQL Server Query to retrieve random records from more than one table(2 or more).
Hello Sir,Can we store css property into database and retrieve it later?
How to disable max,min,close,toolbar option for a webpage