Random Pulls

[SMRieth]

Good day. I am using an MS Access database.

In one table I will have an unknown number of entries. What I would like to
do is pull five unique, random entries and display them. The idea is to pull
five random links from a table of URLs and display them (again, ensuring no two
displayed links are alike).

Any suggestions?

Thank you for your time!

[Mountain Lover]

SELECT yourfields
FROM yourtable
WHERE yoursearchcriteria
ORDER BY RND(youruniqueidfield)

HTH

--
Tim Carley
[url]www.recfusion.com[/url]
[email]info@NOSPAMINGrecfusion.com[/email]

[mxstu]

Originally posted by: Newsgroup User
SELECT yourfields
FROM yourtable
WHERE yoursearchcriteria
ORDER BY RND(youruniqueidfield)


I would also make that .... SELECT TOP 5 yourFields .... to limit the
returned results to 5 records.

[mxstu]

Mountain Lover,

What version of Access/MX are you using? I'm not really that up on Access,
but I know this technique worked in previous CF versions. Yet it doesn't seem
to randomize the records when used with CFQUERY, Access 2000 and MX 6.1.

[SMRieth]

I have tried the usual SQL pulls that have been suggested, but they do not
provide the random pull that I am looking for; they only pull the top 5
entries. I can pull five random entries between CFML and SQL, but I can't
figure out how to make them unique without what is probably excessive code (a
bunch of IF THEN or CASE statements).

I use MS Access 2003.

[Mountain Lover]

>

> What version of Access/MX are you using?

CF5, CF6.1 & CF 7 & Access 2000. Yeah you are right, it doesn't
randomize anymore in CFMX (6&7), CF 5 works perfectly. Gotta' love what
those JAVA drivers did for the M$ Access connection! :(

So something like this will get one random value... should work in CFMX
(untested)
random_element = RandRange(1, queryname.RecordCount);
random_value = queryname.columnname[random_element] ;
you'll need to loop through the query to make a list of 5

Or maybe this:
[url]http://www.cflib.org/udf.cfm?id=524&enable=1[/url]

HTH

--
Tim Carley
[url]www.recfusion.com[/url]
[email]info@NOSPAMINGrecfusion.com[/email]

[BKBK]

>... What I would like to do is pull five unique, random entries
In spite of the simplicity of this statement, I do believe that it is an exceedingly
difficult problem to solve.

[mxstu]

Originally posted by: Newsgroup User
Or maybe this:
[url]http://www.cflib.org/udf.cfm?id=524&enable=1[/url]


Mountain Lover,

I think the UDF would probably work here, if the OP just needs to display 5
random records each time a page is loaded. Of course your original query idea
was better ;-)





<---- NEED TO DOWNLOAD QueryRandomRows() UDF from cflib.org ---->
<!--- get distinct "links" --->
<cfquery name="getRecords" datasource="yourAccessDSN">
SELECT DISTINCT theLink
FROM yourTable
</cfquery>

<!--- get 5 random records --->
<cfset randRecords = QueryRandomRows(getRecords, 5)>
<cfdump var="#randRecords#">

[BKBK]

>... I think the UDF would probably work here
Anyone have any ideas how the problem can be solved using SQL alone?
That would be neat.

[SMRieth]

Thanks to everyone for their help.

I used the longer code I was thinking of before as it seems there is no easier
way to accomplish this.

Basically, I created a second table just to hold the random links. I
generated a random number from 1 up to the total number of links available in
the original table and queried for the link that had an ID that matched the
random number. Then, I ran a query to ensure that ID did not exist in the
second table. If the link did not exist, then it would be entered; if it did
exist, the system will loop until it finds one that doesn't until there are 5
records in the second table.

Oh well, it works. Thanks!

[BKBK]

>... there is no easier way to accomplish this.
This is easy enough, hoping it works. I followed the logic in

[url]http://techrepublic.com.com/5100-9592_11-5796605.html?tag=sc[/url]





/*Assuming your table has column called 'url', of type varchar and non-null*/
CREATE TABLE MyNewTable(PK uniqueidentifier NOT NULL DEFAULT NewID(), url
VARCHAR NOT NULL);
SELECT DISTINCT TOP 5 url, NewID() AS Random
FROM urlTable
ORDER BY Random; DROP TABLE MyNewTable

[Mountain Lover]

Unfortunately Access doesn't support NewID(), SQL Server only. And it
works very well in SQL Server.


--
Tim Carley
[url]www.recfusion.com[/url]
[email]info@NOSPAMINGrecfusion.com[/email]

[BKBK]

>... Unfortunately Access doesn't support NewID(), SQL Server only.
Oops!

[mxstu]

>>This then makes Rnd() a good replacement for MS SQL's NewID().

No. Using Rnd(IDColumn) was the original suggestion by Mountain Lover, but if
you read the thread, you will see that it does not return the anticipated
results in MX 6.1/7, which is what brought on the search for alternative
methods.

>>CREATE TABLE MyNewTable(Random FLOAT NOT NULL, url VARCHAR NOT NULL);
>>SELECT DISTINCT TOP 5 url, Rnd(rowNr) AS Random
>>FROM urlTable
>>ORDER BY Random; DROP TABLE MyNewTable

If the code is just selecting from "urlTable", what is the purpose of the
CREATE TABLE statement here? Also, you cannot ORDER BY an alias in Access.