Random records from database

[Kendo]

Just wondering how other people get around pulling out random entries from a database. This is how I'm currently doing it (but it has a major flaw (for my purposes), which I'll mention later...) :

For example, we'll use tblNews and NewsID

1/ Query DB to get NewsIDs from tblNews
2/ Loop through the query adding each ID into a new array (arrayIDs)
3/ Loop through #arrayIDs#, each time pulling out a random newsID, adding it into a new list (listIDs) and then removing that item from the array...repeat x times.
4/ Do another query on tblNews (with full required select list) using WHERE newsID IN ('#listIDs#')

Which works perfectly. Say I've got 50 records in the table and I want 5 random ones. It works and it's fast.

BUT...

If I have less records, perhaps 10, but I still want 5 random records back. The likelihood is that I'll get the same 5 records every so often...but using the code above they're not going to be displayed in a random order. The same 5 records will always be displayed in exactly the same order.

Does anyone have any suggestions? The best way I've gotten it to work so far was loop UNIONing...which was a joke in terms of execution speed.

 

[pcorreia]

It sounds like the problem is that when you do the second DB query, you're getting the results back in an order determined by the index on the database, which will be the same every time. Here's an idea on how to do it.

<!--- Seed the random number generator --->
<cfset Randomize(Second(Now()))>

<cfloop condition=&quot;ArrayLen(listIDs) GT 0&quot;>

   <!--- Get a random Array Index--->
   <cfset thisIndex = Int(ArrayLen(listIDs)*Rand())+1>

   <!--- Output the query record --->
   <cfoutput query=&quot;qryFullNewsItems>
      <cfif NewsID EQ listIds[thisIndex]>
         <!--- OUTPUT CODE GOES HERE--->
      </cfif>
   </cfoutput>

   <!--- Remove the NewsID we just used --->
   <cfset ArrayDeleteAt(listIDs,thisIndex)>

</cfloop>