Returning A Random Record

[Guest]

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!

[ahmedplummer]

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.

[Guest · Accepted solution]

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>