oxle ID:
password:
store password | forgot password?
schakel over op de nederlandse versie 
 the forums »  INT/Section »  techtalk
 ORDER BY NEWID() in MS Access
This topic is closed Printer Friendly
Author Message
MarcelG
Netherlands 14.8k posts
 Posted 15/01/06 @ 16:56 - 2732 pageviews Show MarcelG's profile  ¤  
Perhaps no one needs this code ever, but I did, so I'll share it here.

I was trying to make a SQL statement, that retrieved 7 records from my database, at random.
I'm using MS Access as database, so I'm limited to the functions available for MS Access.

In SQL, as from version 2000 and up, you can use this statement to get a random selection of records:
code :
SELECT * 
FROM tableName
ORDER BY NewID()

(In SQL 7 this option is available too, but even though NEWID() seems random, it's the same same random order every time... [oO] More info: http://www.sqlteam.com/item.asp?ItemID=8747)

In MySQL you can use this one:
code :
SELECT * 
FROM tableName
ORDER BY Rand()

At first I tried out this last one, slightly modified for Access:
code :
SELECT * 
FROM tableName
ORDER BY Rnd()

However, even though the results seemed random, they were the same 'random' order each time I ran the query.

However, after some googling I found out that more people sought this solution, and fortunately someone came up with a working statement:
code (with markup) :
SELECT * 
FROM tableName
ORDER BY
Rnd(-(1000*driverID)*time());
The negative value passed to the RND function forces it to give back a really random number.

The driverid should be one of the columns available in the table containing an integer.

My final SQL statement ended up to be this:
code :
SELECT TOP 7 FEED_URL, FEED_ID, FEED_NAME, FEED_REFRESH 
FROM FORUM_FEEDS 
ORDER BY Rnd(-(1000*FEED_ID)*Time());


I hope someone might stumble on this topic, and find it usefull for themselves. [exit]
sr_erick
 
USA 546 posts
 Posted 17/01/06 @ 05:47 Show sr_erick's profile  #1 
I sort by randid() for my image gallery when I want random images for the homepage. It is VERY innefficient after you get a few hundred to a thousand records. I've got over 6000 records in that table. I need to find a better way to do it but I've been too lazy to work on it as of late.
MarcelG
 
Netherlands 14.8k posts
 Posted 17/01/06 @ 10:03 Show MarcelG's profile  #2 
Is there a difference in using ORDER BY RANDID() and ORDER BY NEWID() in MS SQL ?

Just a thought ; you might want to create an array once a day, filled with a random ordered list of the image ID's, and store that array in an application variable.
Then, each time an image is retrieved, you get the first id from that array, and remove it from the array, and save the variable once again.

When the array contains less than let's say 5 ID's, recreate a new random filled array, and restart the entire circle.

In that way, your randomization occurs not on every image retrieval, but only every for instance 1000 retrievals.
  This topic is closed Printer Friendly  
 This topic has been archived. Posting replies is no longer possible. 

Instead, you can post a new topic here.

oxle
parsetime: 0.31s
click here to support this site
© 2003 - 2014 Marcel Göertz
Powered by Snitz Forums 2000