szehau's weblog Life, Internet, Software, Gadgets, Programming and Investments

MySQL randomly sort query result

How do you choose records randomly from MySQL database. Normally we will select all the related data from database and then store it to a buffer array. From the array, we randomly choose the records that we want.

There is a simpler way. Let say we have the following query which return 100 rows of record and we want to randomly choose 5 rows from it:

select column1, column2 from table1 where condition1 and condition2

We can simply tweak the query to return 5 rows randomly.

select column1, column2, rand () as randomColumn from table1 where condition1 and condition2 order by randomColumn limit 5

That's it! We simply add a column with random values and the sorted by the randomColumn. This method is pretty useful for application such as Rotating Ads, Random Links/Post and etc.

Note: Do not use this method on query that return large amount of rows (e.g. more than 1 millions rows), it will slow down the overall execution time.

Most Commented Posts

• Default Trackbacks
• Snow Wolf Lake
• Working Holiday at New Zealand – The journey begun
• Enable .htaccess and url rewriting in Apache for Windows
• Snow Wolf Lake in Malaysia
• Working at Rakaia
• Get banned by PPS
• Google Adsense Cheque
• Wordpress 2.0 upgraded successfully
• Recent Wordpress Posts in Sidebar