Getting n random records from database
Tags: Coldfusion
I came across a problem where I needed to get 10 random records from the database, my original approach that I used a few years ago worked fine for small amount of records but when I had 18,412 records it just didn't cut it anymore. So what did I do, well I was not getting anywhere and ended up finding a solution that would store it a temp table this worked but I wasn'y happy still. I avoid temp tables as much as possible, so did some further study and found the solution to very easy.
SELECT TOP 10 *
FROM table
ORDER BY NEWID()
FROM table
ORDER BY NEWID()
Now that is the SQL Server version, and the following although untested on V4.0 of mySQL this will work on mySQL V5.0
SELECT id FROM table ORDER BY RAND() LIMIT m
There are no comments for this entry.
TweetBacks