Arachn1d
asked on
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[CurrentRec
</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
<!--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[CurrentRec
</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
ASKER
The problem with your proposed solution is that there is still nothing preventing the same random recordnumber from being picked twice.
Nick
Nick
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.
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.
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
:) dapperry
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!!!
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!
Thanks a lot!
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.