How to efficiently get a random set of records from a DB

[Laphan]

Hi All

I use a MySQL DB with my ASP classic web app.

I've been asked if I can create a routine whereby I get a random number of
products (records) from the DB and display these on the site. Basically
every time a visitor hits the home page, they want the site to display a
random selection of say 6 products.

I have no problems getting the data and displaying it, my problem is
grabbing 6 random records efficiently.

My initial thought was bring back all of the records to a local array (like
I normally do) and then pick 6 random records in VBscript, but if a DB has
12,000 products this is a silly server hit to bring them all back just to
display 6 out of the 12,000.

The products table doesn't have an auto-number field, the unique-ness is
managed by the product's own unique stock code, but this isn't a sequential
number - its alphanum.

Any ideas on how I can get 6 randoms from the DB rather than bringing all
locally?

Thanks

[Bob Barrows [MVP]]

Laphan wrote:

Hi All

I use a MySQL DB with my ASP classic web app.

I've been asked if I can create a routine whereby I get a random
number of products (records) from the DB and display these on the
site. Basically every time a visitor hits the home page, they want
the site to display a random selection of say 6 products.

I have nothing to add to this:
http://www.aspfaq.com/show.asp?id=2132
.... especially since I'm not familiar with the SQL dialect used by MySQL

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Captain Paralytic]

On 21 Sep, 11:48, "Laphan" <[e-mail] am.comwrote:

Hi All

I use a MySQL DB with my ASP classic web app.

I've been asked if I can create a routine whereby I get a random number of
products (records) from the DB and display these on the site. *Basically
every time a visitor hits the home page, they want the site to display a
random selection of say 6 products.

I have no problems getting the data and displaying it, my problem is
grabbing 6 random records efficiently.

My initial thought was bring back all of the records to a local array (like
I normally do) and then pick 6 random records in VBscript, but if a DB has
12,000 products this is a silly server hit to bring them all back just to
display 6 out of the 12,000.

The products table doesn't have an auto-number field, the unique-ness is
managed by the product's own unique stock code, but this isn't a sequential
number - its alphanum.

Any ideas on how I can get 6 randoms from the DB rather than bringing all
locally?

Thanks

Ask this over on comp.databases. mysql.
Even better, just search its archives, you will find the answer to
this question, there.

[Ted Dawson]

"Laphan" <pl****@dont.sp am.comwrote in message
news:eH******** ******@TK2MSFTN GP06.phx.gbl...

Hi All

I use a MySQL DB with my ASP classic web app.

I've been asked if I can create a routine whereby I get a random number of
products (records) from the DB and display these on the site. Basically
every time a visitor hits the home page, they want the site to display a
random selection of say 6 products.

I have no problems getting the data and displaying it, my problem is
grabbing 6 random records efficiently.

My initial thought was bring back all of the records to a local array
(like
I normally do) and then pick 6 random records in VBscript, but if a DB has
12,000 products this is a silly server hit to bring them all back just to
display 6 out of the 12,000.

The products table doesn't have an auto-number field, the unique-ness is
managed by the product's own unique stock code, but this isn't a
sequential
number - its alphanum.

Any ideas on how I can get 6 randoms from the DB rather than bringing all
locally?

Thanks