Uniquely selecting random records

I am trying to randomly select and display 20 records from a table. Currently, my code looks something like this (simplified):

<!--Titles is a query containing all possible records to choose from-->
<cfloop index="counter" from="1" to="20">
  <cfset CurrentRecord = RandRange(1, Titles.RecordCount)>
  <!--Display data, using Titles.Whatever[CurrentRecord]-->

The problem is that I want a simple way to pick 20 random records, but make sure that I dont pick the same record twice in any one run of the script. I DON'T want to have to add the ID of each to an array and have to recursively search the array for the ID to see if it is already there.

Who is Participating?
dapperryConnect With a Mentor Commented:
Try something like this:
<cfquery name="test" datasource="test">
     SELECT TOP 10 *
     FROM Projects
     ORDER BY Rnd(ProjID)

Let me know if it works for you.  It works in Access, but I don't have time to check to see if it works through cfquery and odbc.

:) dapperry
This may not work for your situation but why not
1. Create a random list 1 - Recordcount
2. Then simply store the list and reference the list each time you want to go to the next random numbers.

If this is not an answer please tell me what you are using the random numbers for and how and I will try to help you.
Arachn1dAuthor Commented:
The problem with your proposed solution is that there is still nothing preventing the same random recordnumber from being picked twice.

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Well no that is not true that is why I asked you to explain...


you take a list of numbers and then randomly select them.

1. 1 - 10 store them in a list

2. pick a random number between 1 - 10

3. 6

4. set randomnum[1] = ListNum[6]

5. delete ListNum[6]

6. pick a random number between 1 - 9

7. 3

8. set randomnum[2] = ListNum[3]

9. delete ListNum[3]

10. pick a random number between 1 - 8

11. 6

12. set randomnum[3] = ListNum[6]

13. delete ListNum[6]

Now I understand their are some situations this will not work but I need to know how your going to use it to create the code.  This will work for random banner ads and some other situations.
What are you using for a database?  For instance I believe you can incorporate the Rnd function into an Access query, and the use a TOP 10 to get the ten randomly selected recs.

:) dapperry
Arachn1dAuthor Commented:
Yes, I am using access for a database, so if you know a way to sort into a random order then pick the first x, that would be brilliant.
And nathans, I will look into that possibility, thanks. It just occurred to me, is it possible to delete records from a query and not the underlying database? That would have the same effect.

Arachn1dAuthor Commented:
Wow, it does work, too! Although I'd like to know why - If for example, rnd(TitleId) is 43, isnt that like saying
ORDER BY 43? That wouldn't make sense.
Thanks a lot!!!
This also solved my prob. I was trying to get last 10 records from the Database.

Thanks a lot!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.