Random record selection

[Newsgroup_User]

I want to be able to grab a random record from the database and display it
on my front page of my web site. I thought I would just was a RandRange and
set it from 1 to the recordcount of the table. Worked great at first.

Then I realize that records are going to be deleted. So I added a check to
see if that record exists. If it does, display, if not choose again. Seemed
to work like a charm. I was wrong.

If there are records (auto incremented) and I remove one and then do a
recordcount, I will be one short, etc. Is there a way to do a random records
selection based on the number of the record verses the actual incremented
number of the id.

The 6th record could be #6, #12, #13

To make a long post short, too late, how can I choose the 6th or 9th or 34rd
record.
--
Wally Kolcz
Developer / Support

[Newsgroup_User]

Instead if recordCount run a Select query like

SELECT MAX(IDfield)
FROM aTable

This will return the largest auto-generated ID, you will still have to
check for missing ones.

There are probable better overall methods to do what you desire, but
this is a quick fix for you.

[davidsimms]

Wally,

Simply select a single record at random directly from the table, rather than to get into checking for its existence etc. Use something like the following query to do this:

SELECT TOP 1 *
FROM your_table_name
ORDER BY NewID()

David Simms

[Newsgroup_User]

MySQL:
SELECT yourfield FROM yourtable WHERE ... ORDER BY rand() LIMIT 1

MSSQL:
SELECT TOP 1 yourfield FROM yourtable WHERE ... ORDER BY newid()

MS Access:
SELECT TOP 1 yourfield FROM yourtable WHERE ... ORDER BY
RND(yourprimarykey) [warning: this may not be random through CF]

HTH
--
Tim Carley
www.recfusion.com
info@NOSPAMINGrecfusion.com

[Newsgroup_User]

Thanks, that seem to work fine