I think I just had a page and a half of coldfusion code which generated 5
random numbers reduced to a single SQL statement. Before I choke on a
green pepper from my subway sandwhich, can you tell me how the newid
function in SQL works? I ran a test of select top 1000 and did not get any
duplicates (verified in access). Will the SQL statement below always be
unique values? If it did not generate unique values for some reason I would
assume select distinct top 1000 would ensure uniqueness. My real question
is: How does the newid function in SQL always know to pull 5 random numbers
each time. It seems as though if you ran the query twice in a row you
would get the same top 5, but you dont.
CC
"Cruz, Joseph" <[EMAIL PROTECTED]> on 08/01/2001 01:58:17 PM
Please respond to [EMAIL PROTECTED]
To: CF-Talk <[EMAIL PROTECTED]>
cc:
Subject: RE: Random in SQL
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hey, BT.
If you're just interested in a random 5 items from a straight SELECT
statement,
try:
SELECT TOP 5 item_id
FROM base_table
ORDER BY NEWID()
:)
Or, a more ANSI-SQL compliant syntax:
SELECT TOP 5 item_id, newid() as new_id
FROM base_table
ORDER BY new_id
Either way, SQL server creates an aliased column of data type
uniqueidentifier.
HTH!
Joe
> -----Original Message-----
> From: BT [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, August 01, 2001 10:47 AM
> To: CF-Talk
> Subject: Random in SQL
>
>
>
>
>
>
> I am trying to get a random 5 items in SQL on SQL7 instead of
> on the cf side....
>
>
>
> Anyone know?
-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 7.0.3 for non-commercial use <http://www.pgp.com>
iQA/AwUBO2hSB9CGp5RuWHIBEQJbfQCg+Xwx9sIm8z0lwTJrIgmjjyh9B1cAoKgL
/6k1hXmONZY8Sr7C1h5npV8r
=tEQO
-----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
