random record

[anorakgirl]

hey all,

desperately trying to retrieve a random record from a table. i know i can do it by creating a recordset containing all rows, generating a random number then moving to that row, but if its a big table, surely thats going to be slow as it must have to retrieve all the records up to the one i want.

i was wondering if its possible in access to do a query with a limit 1, offset n type clause, or something like where recordnumber = n

there must be some way to do this efficiently? please...

thanks 4 any help,
AnorakGirl

~

http://www.anorakgirl.co.uk

~

 

[foxbox]

Interesting problem. I have a somewhat similar situation:
my application must draw 6 random crews in a heat (it's rowing). I use an identity field for "crewnumber". Before i start the draw i must determine the highest possible value for 'crewnumber' (each event starts with an empty database, so the first crewnumber is 1). originally i use a seperate SQL-select for that:
select top 1 crewid from crews order by crewid desc

later on i changed the application. i use a 'systemtable' to hold several values (path names, colors, titles, etc). an i reserved a field to hold the highest crewnumber (with sql this is easy with a trigger or stored procedure). so i retrieve my high value from this table. the rest is simple:


' initialize the randomizer:
randomize timer/rnd

function GiveCrewNumber( nHigh )
GiveCrewNumber = int( nHigh * rnd + 1 )
end function


[of course there is more to it in my application, because i do not want the same crew more than once in a heat, etc]





br
Gerard

 

[anorakgirl]

thanks foxbox, i did try something like that as each row in my table does have an id field which is an autonumber, so i could just generate a random no between 1 and the max, and then retrieve that record like you say.

however unfortunately people can also delete rows from my table, and once there are gaps in the numbering that won't work. thats why i was wondering if you could select by some internal "recordnumber" variable instead...

cheers anyway!
AnorakGirl


~

http://www.anorakgirl.co.uk

~

 

[anorakgirl]

more info:

i found this query:

SELECT top 1 * from tablename ORDER BY Rnd(IsNull([fieldname])*0+1)

if i open and close it in access, i get a different record each time. but if i use it in my asp page and refresh the page, i get the same record each time. anyone know why that could be? (i'm not caching pages or anything like that)

ta!
AnorakGirl ~

http://www.anorakgirl.co.uk

~

 

[foxbox]

i suppose the rnd() in your asp page is always generating
the same number (it's not random at all). that's why a randomize with 'timer/rnd'.... br
Gerard

 

[link9]

Check this out:

http://planetsourcecode.com/xq/ASP/txtCodeId.6201/lngWId.4/qx/vb/scripts/ShowCode.htm

I think it should fit your needs -- and sign up for their mailing list, too. You'll get some new source code in your inbox every day and every bit of it is free and distibutable.


Paul Prewett

 

[anorakgirl]

thanks link9 - i've tried something similar and it does work, but i'm concerned that because it opens the whole table as a recordset and then moves to a certain position in it, it might get slow when my tables get big. dunno, could be wrong, not sure how asp/access handle recordsets before you actually access the data in them. anyone?

cheers for the tip about the website too, havin a look now...

AnorakGirl

~

http://www.anorakgirl.co.uk

~

 

[link9]

PRNGMIT.dll

search for that file on the site. It came on that mailing list about a month back, and it is a library created to truly randomize random numbers. I use it, and I've been quite happy with the results. It will come with a readme and full demonstration of its capability.

Maybe you can use that to implement the function you described earlier but kept getting the same numbers.


Paul Prewett