ORDER BY NEWID() returning duplicates records

This returns 2 random records for me. However, on occasion, the 2 records
returned are identical. Does anybody have any thought for preventing this
from happening?
SELECT TOP 2 P.*, C.CatID FROM JEP_tblProducts P, JEP_LtblProductCategories
C WHERE P.Image1<>'' AND C.ProductID = P.ProductID ORDER BY NEWID()
TIA,
TonyGThat's because SQL evaluates NewId() only once in your query. If you are try
ing
to randomly return two records try this:
Select TOP2 NewId(), P.*, C.CatId
From JEP_tblProducts As P
, JEP_LtblProductCategories As C
Where P.Image1 <> ''
And C.ProductId = P.ProductId
Order By 1
Thomas|||Hi
It could be because the join results in 2 identical records in some instance
s.
Run the query with no TOP clause and see how that compares to the number of
records in the tables.
You may want to update your query to the ANSI style join:
SELECT
TOP 2
P.*,
C.CatID
FROM
JEP_tblProducts AS P
INNER JOJN JEP_LtblProductCategories AS C
ON C.ProductID = P.ProductID
WHERE
P.Image1<>''
ORDER BY NEWID()
Regards
Mike
"TonyG" wrote:

> This returns 2 random records for me. However, on occasion, the 2 records
> returned are identical. Does anybody have any thought for preventing this
> from happening?
> SELECT TOP 2 P.*, C.CatID FROM JEP_tblProducts P, JEP_LtblProductCategorie
s
> C WHERE P.Image1<>'' AND C.ProductID = P.ProductID ORDER BY NEWID()
> TIA,
> TonyG
>
>|||>> That's because SQL evaluates NewId() only once in your query.
Generally, any non-numeric expression used in the ORDER BY clause is
evaluated in the same way the expression is evaluated in the SELECT clause.
And it is applicable for NEWID() as well.
The duplication OP has is perhaps due to lack of keys/constraints in the
tables and/or the result of using non-unique columns in the JOIN clause. But
then without seeing any useful DDLs, it would be anyone's guess.
Anith|||Thats not true, using the newid () funtion in the order generates for every
row in the query a seperate GUID , every time you execute is. There is a
problem with your query it returns more than one row becasue your table in
not only related to the second table via one column.
Try to select this and youll see that there will be more than one row
returned:
Select count(*) from
> From JEP_tblProducts As P
> , JEP_LtblProductCategories As C
> Where P.Image1 <> ''
> And C.ProductId = P.ProductId
Perhaps you can make a join with the appropiate key in it.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Thomas" <thomas@.newsgroup.nospam> schrieb im Newsbeitrag
news:Onzdy%23nSFHA.248@.TK2MSFTNGP15.phx.gbl...
> That's because SQL evaluates NewId() only once in your query. If you are
> trying to randomly return two records try this:
> Select TOP2 NewId(), P.*, C.CatId
> From JEP_tblProducts As P
> , JEP_LtblProductCategories As C
> Where P.Image1 <> ''
> And C.ProductId = P.ProductId
> Order By 1
>
> Thomas
>|||Well, you have a 1-M relationship. Thus, it's possible to return 2 that are
identical. The newid() only randomizes the resultset and does not remove the
dupes.
Here is a trick to get the uniqueness.
SELECT TOP 2 *
from (select distinct
P.*, C.CatID FROM JEP_tblProducts P, JEP_LtblProductCategories
C WHERE P.Image1<>'' AND C.ProductID = P.ProductID
) derived
ORDER BY NEWID()
-oj
"TonyG" <groups@.RE-MO-VE-globalmagic.com> wrote in message
news:OUBU05nSFHA.3088@.TK2MSFTNGP15.phx.gbl...
> This returns 2 random records for me. However, on occasion, the 2 records
> returned are identical. Does anybody have any thought for preventing this
> from happening?
> SELECT TOP 2 P.*, C.CatID FROM JEP_tblProducts P,
> JEP_LtblProductCategories C WHERE P.Image1<>'' AND C.ProductID =
> P.ProductID ORDER BY NEWID()
> TIA,
> TonyG
>|||I stand corrected. Is there is any way to know ths for sure (documentation,
proof of concept etc)?
Thomas
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eGCxkEoSFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Generally, any non-numeric expression used in the ORDER BY clause is evalu
ated
> in the same way the expression is evaluated in the SELECT clause. And it i
s
> applicable for NEWID() as well.
> The duplication OP has is perhaps due to lack of keys/constraints in the
> tables and/or the result of using non-unique columns in the JOIN clause. B
ut
> then without seeing any useful DDLs, it would be anyone's guess.
> --
> Anith
>|||Build an example myself. Thanks for the correction.
Thomas
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eGCxkEoSFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Generally, any non-numeric expression used in the ORDER BY clause is evalu
ated
> in the same way the expression is evaluated in the SELECT clause. And it i
s
> applicable for NEWID() as well.
> The duplication OP has is perhaps due to lack of keys/constraints in the
> tables and/or the result of using non-unique columns in the JOIN clause. B
ut
> then without seeing any useful DDLs, it would be anyone's guess.
> --
> Anith
>|||Thanks, that did the trick.
I knew I was missing something.
TonyG
"oj" <nospam_ojngo@.home.com> wrote in message
news:OJSm6JoSFHA.2000@.TK2MSFTNGP10.phx.gbl...
> Well, you have a 1-M relationship. Thus, it's possible to return 2 that
> are identical. The newid() only randomizes the resultset and does not
> remove the dupes.
> Here is a trick to get the uniqueness.
> SELECT TOP 2 *
> from (select distinct
> P.*, C.CatID FROM JEP_tblProducts P, JEP_LtblProductCategories
> C WHERE P.Image1<>'' AND C.ProductID = P.ProductID
> ) derived
> ORDER BY NEWID()
> --
> -oj
>
> "TonyG" <groups@.RE-MO-VE-globalmagic.com> wrote in message
> news:OUBU05nSFHA.3088@.TK2MSFTNGP15.phx.gbl...
>