Select Random Records in MySQl, SQL Server & Oracle, etc

Posted At : January 24, 2008 9:02 AM | Posted By : Tami
Related Categories: ColdFusion, Internet, Databases, snippets

Quite often a user will want a random display of items from his database. Ads, Thumbnails, Topics... whatever.... Here are some quick queries that can perform just that task for you:

Scenario: I want to select 5 records from my famous-foo table in no particular order, and I have no idea how many records are there....

In MySQL:


select * from famous-foo order by rand() limit 5

In SQL Server (7, 2000+):


select top 5 * from famous-foo order by newid()

In Oracle:


select * from famous-foo sample(5)
(note this returns 5% of records, so you'll have to handle potentially extra records in your code, but is a tad quicker than creating an inline view as below:)

select * from (select * from famous-foo order by dbms_random.value) where rownum = 5

In PostreSQL


select * from famous-foo order by random() limit 5

Comments (0) | Print | Send | del.icio.us | Digg It! | Linking Blogs | 883 Views
Comments (Comment Moderation is enabled. Your comment will not appear until approved.)

There are no comments for this entry.

[Add Comment]
BlogCFC was created by Raymond Camden. This blog is running version 5.9.1.002. Contact HHWD