RE: (SQL) Selecting Random Record From Database

2001-08-16 Thread Cruz, Joseph 

 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

You'll actually need to add an order by new_id clause at the end of that
statement to consistently return a random record (otherwise you just get the
first record in the table).

SELECT TOP 1 employee_id, newid() as new_id
FROM yourtable
ORDER BY new_id

There is a largish caveat for this method.  The entire table is scanned and a
newid generated for each row and then ordered before you get a record back, so
tread lightly with this in VLDBs.

Good luck!

Joe

Joseph Cruz
Programmer/Analyst
3620 Locust Walk, suite 400
Philadelphia, PA  19104-6302
215-898-1220 (work)
215-308-0657 (pager)
215-768-2071 (cell)
[EMAIL PROTECTED]
 

 -Original Message-
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] 
 Sent: Thursday, August 16, 2001 10:02 AM
 To: CF-Talk
 Subject: Re: (SQL) Selecting Random Record From Database
 
 
 Someone posted this idea a while back.
 
 SELECT TOP 1 employee_id, newid() as new_id
 FROM yourtable
 
 CC
 
 
 
 
 Steve Reich [EMAIL PROTECTED] on 08/15/2001 10:54:11 PM
 
 Please respond to [EMAIL PROTECTED]
 
 To:   CF-Talk [EMAIL PROTECTED]
 cc:
 Subject:  (SQL) Selecting Random Record From Database
 
 
 How do I select a single, truly random (random seed) record 
 from my database?
 
 Thanks,
 Steve

-BEGIN PGP SIGNATURE-
Version: PGPfreeware 7.0.3 for non-commercial use http://www.pgp.com

iQA/AwUBO3vT3tCGp5RuWHIBEQJipACg69fgnDLnbH3PpbX2O8asRscyDy0AoMJS
6mkuECOO1islJJLJeiqtJTUy
=5Es6
-END PGP SIGNATURE-

~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: (SQL) Selecting Random Record From Database

2001-08-16 Thread Steve Reich 

 Someone posted this idea a while back.

 SELECT TOP 1 employee_id, newid() as new_id
 FROM yourtable

 CC

That pulls the first1 record from the database. This will pull the first
1000 records...

SELECT TOP 1000 employee_id, newid() as new_id
FROM yourtable

I need a random record. Isn't there a rand funcion in SQL perhaps
something like

SELECT Rand(employee_id) as RandomRecord
FROM yourtable

Seems like there's gotta be a simple solution.

Thanks,
Steve




~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists