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]-->
</cfloop>

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.

Nick
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Senior ProgrammerCommented:
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.
Author Commented:
The problem with your proposed solution is that there is still nothing preventing the same random recordnumber from being picked twice.

Nick
Senior ProgrammerCommented:
Well no that is not true that is why I asked you to explain...

EX:

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.
Commented:
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
Author 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.

Nick
Commented:
Try something like this:
<cfquery name="test" datasource="test">
SELECT TOP 10 *
FROM Projects
ORDER BY Rnd(ProjID)
</cfquery>

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

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author 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!!!
Commented:
This also solved my prob. I was trying to get last 10 records from the Database.

Thanks a lot!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.