How to randomly retrieve a record set from the database?

Random recordset of ASP

[Raymondwu]

Hi, I'm a new comer. Can anyone teach me how to write an ASP script to randomly retrieve a set of records from the database? I want to randomly select four records from a MS Access table, with the table column "FEATURED" marked as "Yes" and then display the table contents on the screen. So that the user will see different result each time they open the page.

And the second problem is how to resolve the problem of duplicate items since I will randomly select 4 records. Item 1 and item 3 may be the same record if I don't put any control on it.

Any help will me most appreciated.

[duckhunter]

I've had to do this a few times. If your data is in XML, it's easier as you do not have to open a Static recordset and movefirst which can be expensive but here's one way to do it using an ADO Recordset.

Dim cn
Dim rs
Set cn = server.CreateObject("ADODB.CONNECTION")
Set rs = server.CreateObject("ADODB.RECORDSET")
strConnect = "<your connection here>"
cn.Provider = "<Your provider here>"
cn.Open strConnect

strSQL = "SELECT TOP 10 * FROM <YourTableHere>"

rs.CursorType = adOpenStatic
Set rs = cn.Execute(strSQL)

'Initiate Variables (Overclocking, DesiredNumber Of Results and the Unique String)
iOverClock = 0
iNumberOfResults = 3
strUnique = "*"
For x = 1 to iNumberOfResults

'Overclock value to avoid getting stuck on repeated random selection of a single value
iOverClock = iOverClock + 1
If iOverClock > 6 Then
'Avoid race condition. Don't try too hard.
exit for
End If

'Generate A Random Number between 1 and 10 (10 set below in the Int((10 * Rnd....)
Randomize()
MyValue = Int((10 * Rnd())+1)
If Instr(1,strUnique, "*" & Cstr(MyValue) & "*") > 0 then
'For Debugging Online I am writing out Skipping
'If a duplicate is found, set x = x-1 and try again
Response.Write "Skipping<BR>"
x = x - 1
else
For y = 1 to (MyValue-1)
rs.MoveNext
Next
'Unique Value Found - Write Out Values you Need
Response.Write "Unique Record: " & x & "Value: " & rs.Fields(0).Value & "<BR>"
strUnique = strUnique & "*" & Cstr(MyValue) & "*"
'Now move back to the first record for the next selection
rs.MoveFirst
End If
Next

rs.Close
cn.Close
set rs = nothing
set cn = nothing

[Raymondwu]

Wow! That's great! Thank you very much.

I'm trying to understand the scripts and have two questions.

1. "SELECT TOP 10 * FROM <table>" - I've never seen "TOP 10 *" before. How is this SQL statement functioning?

2. It seems that the logic is to randomly generate a number (MyValue) from 1 to 10, and then read (MyValue) times from the top of the table. This logic is already good enough for me. But I'm thinking that it will limits to the first 10 records from the table, am I right? I'm just thinking that is that possible to allow all records in the table have the chance to be selected? A solution but I'm not quite sure is:

- Select all neccessary records from the table
- Set the pointer to the top first record
- Set a flag to readnext
- Repeat following until number of results retrieved
- generate a random number (MyValue) from 0 to 5
- read next records by MyValue times, but if eof is reached in the read next loop, change to read previous and continue the reading loop. (or vice versa depending on the curent value of the flag)
- Get the record, display it, and remember the unique keyfield into strUnique
- But if the record is already used, discard it and try the loop again.
- And apply the Overclock checking

Do you think it works?

(sorry we are not in the same time zone that I may not able to response your replies immediately)

[duckhunter]

We're not too far off or I work too much! Yes, the Top 10 is the first 10 records from a SQL table and was intended for example.

That method sounds fine. You have to somewhat base your random number range on how many records are in the table and how many results you want. If you have 100 records and want 5 results then the number is between 1 and 20. That way you are always making it to the end of the recordset to give everyone a fair shot.

You could do .movelast and get the recordcount, divide that by the number of result required and that's your variable to plug into your formula. You should only reach EOF at worst.

[Raymondwu]

Oh! That also sounds good. Thank you very much.

[semick]

Too bad you aren't using MS-SQL - you could do something like this:

select top 100 newId() as Id,* from product order by Id

The newId() function generates a random GUID - will give you a different order each time...

(and with minimal code)

Scott Emick

OK - this works in Access! [yes I keep my software whereabouts in a db]

SELECT Rnd(1+0*(Len(version))) AS RndId, *
FROM software
ORDER BY Rnd(1+0*(Len(version)));

The key with access is you need to pass in SOMETHING to the random function to get a different random number for each row. In my case I just passed in the length of a field and that did the trick...

Now you could clean the SQL up or hide the random field etc...Just order by it

my output the first time:

RndId,Title,CD Number,Developer,Version,Filename
0.312288105487823,Windows 2000 Pro,2,Microsoft,2000,Win2kPro.zip
0.798773527145386,Norton Antivirus,1,Symantec,2002,Norton Antivirus 2002 Full.zip
0.151756227016449,Corel Draw,1,Corel,10,corel_draw_10_full_with_serial.zip
0.592991232872009,Partition Magic,1,PowerQuest,7,Partition Magic 7 Pro w- serial(1).zip
0.956236898899078,PC Anywhere,1,Symantec,10.5.1,Symantec.PcAnywhere.v10.5.1.Corporate.Edition-SoS.zip
0.243147850036621,Photoshop,3,Adobe,6.0,Adobe PhotoShop 6.0.zip
0.939971148967743,Ghost,1,Symantec,6.5.1,Ghost651.zip
0.114332795143127,Omnipage Pro,1,scansoft,11,omnipage pro 11 full retail iso english.zip
0.984253227710724,Acrobat,1,Adobe,5,Adobe Acrobat 5.zip
0.632975816726685,Director,1,Macromedia,8,Director 8.zip
0.598856389522552,Windows ME,2,Microsoft,ME,WINME.zip
0.902569651603699,Easy CD Creator Platinum,1,Adaptec,5.0,Adaptec Easy CD Creator Platinum 5.0.zip
0.57486492395401,Freehand,1,Macromedia,9,Macromedia Freehand 9.0.zip
0.245173931121826,Hoyle Card Games,1,Hoyle,2001,Hoyle Card Games 2001.zip
0.860162436962128,Office XP,3,Microsoft,10,root
7.49503374099731E-02,Norton Utilties 2001,1,Symantec,2001,Symantec_Norton Utilities 2001.zip
0.438826382160187,Server Magic,1,PowerQuest,4.0,ServerMagic 4.0.zip
0.759979009628296,Flash,1,Macromedia,6.0,Macromedia Flash 6 MX full final with serial.zip
0.245678842067719,Easy Cd Creator,3,Adaptec,5.0,Adaptec Easy CD Creator Platinum 5.0.zip

and the second time:

RndId,Title,CD Number,Developer,Version,Filename
0.207593619823456,Omnipage Pro,1,scansoft,11,omnipage pro 11 full retail iso english.zip
0.364688038825989,Flash,1,Macromedia,6.0,Macromedia Flash 6 MX full final with serial.zip
0.57351952791214,Easy Cd Creator,3,Adaptec,5.0,Adaptec Easy CD Creator Platinum 5.0.zip
0.344180822372437,Windows ME,2,Microsoft,ME,WINME.zip
0.528229653835297,Norton Antivirus,1,Symantec,2002,Norton Antivirus 2002 Full.zip
0.425485014915466,Acrobat,1,Adobe,5,Adobe Acrobat 5.zip
0.573013365268707,Freehand,1,Macromedia,9,Macromedia Freehand 9.0.zip
5.00450134277344E-02,PC Anywhere,1,Symantec,10.5.1,Symantec.PcAnywhere.v10.5.1.Corporate.Edition-SoS.zip
0.547599971294403,Corel Draw,1,Corel,10,corel_draw_10_full_with_serial.zip
0.206485390663147,Director,1,Macromedia,8,Director 8.zip
0.962678253650665,Partition Magic,1,PowerQuest,7,Partition Magic 7 Pro w- serial(1).zip
0.93305230140686,Office XP,3,Microsoft,10,root
0.992572247982025,Hoyle Card Games,1,Hoyle,2001,Hoyle Card Games 2001.zip
0.839586138725281,Easy CD Creator Platinum,1,Adaptec,5.0,Adaptec Easy CD Creator Platinum 5.0.zip
0.409323275089264,Ghost,1,Symantec,6.5.1,Ghost651.zip
0.805275440216064,Photoshop,3,Adobe,6.0,Adobe PhotoShop 6.0.zip
0.989428222179413,Windows 2000 Pro,2,Microsoft,2000,Win2kPro.zip
0.258442044258118,Server Magic,1,PowerQuest,4.0,ServerMagic 4.0.zip
0.174484074115753,Norton Utilties 2001,1,Symantec,2001,Symantec_Norton Utilities 2001.zip

[Raymondwu]

Thanks Semick.

select Rnd(1+0*(Trim(Len(Prodcode)))) AS RndId,* from Product where Prodfeatured=TRUE order by Rnd(1+0*(Trim(Len(Prodcode))))

I've tried your method as above but the Engine return the following error...

Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters

/chingbee/magic/share/Public.asp, line 355