Skip to main content
A
Anonymous
September 26, 2008
Answered

Returning A Random Record

  • September 26, 2008
  • 1 reply
  • 300 views
I want to cfquery a random record from a table. I can't randrange the key field because some keys are not there. I've seen a suggestion to load the keys into a table, grab a random instance, then query with that key but that seems like a long way around to me. There could be thousands of record in this table.

Any ideas? Thanks!
This topic has been closed for replies.
Correct answer
only access, no sql.
this is the code i am using.

<cfquery name="xxxx" datasource="dddd">
SELECT * FROM zzzz
</cfquery>

<cfset randomrow = randrange(1,xxxx.recordcount)>

<cfoutput query="xxxx" startrow=#randomrow# maxrows=1>
</cfoutput>

1 reply

A
ahmedplummer
Participant
September 26, 2008
if you are using mssql:
select top 1 *
from your_table_name
order by newid()

google newid() and you can learn the details under the hood.
A
Anonymous
Correct answer
September 27, 2008
only access, no sql.
this is the code i am using.

<cfquery name="xxxx" datasource="dddd">
SELECT * FROM zzzz
</cfquery>

<cfset randomrow = randrange(1,xxxx.recordcount)>

<cfoutput query="xxxx" startrow=#randomrow# maxrows=1>
</cfoutput>
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices