ORDER BY NEWID() Problem

[StevieMars]

I have the follwoing query:

SELECT Column1, Column2
FROM Table1
WHERE 
Table1.Column1 IN (
    SELECT TOP (5) Column1
    FROM Table2
    ORDER BY NEWID()
)

My problem is that the Top(5) part of the query is being ignored and instead a random number of rows is returned. Somtimes no rows, sometimes 5, sometimes 10. If I change the Order by NEWID() to Order by Column1 then the Query works fine and the expected number of rows is returned. This leads me to think there is some kind of conflict between the Top function and the Order by NEWID().

Any ideas what might be happening?

Thanks,
Steve

[ck9663]

When you said the TOP 5 of the query is ignored, are you talking about the subquery or the entire query? Since your ORDER BY will generate a random number every time, the subquery will return 5 different rows every time. Therefore, your IN clause will return unexpected result as the list of the Column1 in your subquery can change every time you run it.

Hope that make sense...

Happy Coding!!!

~~CK

[Jerry Winston]

CK nailed it.

There's nothing sequential about NEWID() that's just not what it does. What were you trying to accomplish by using ORDER BY NEWID()?

[StevieMars]

It's the top 5 of the subquery. There are 5 rows that should be returned by the subquery but I need them in a random order. I can order by a specific column and the subquery will return 5 rows but when ordering by NEWID I get a different number of rows each time from the subquery.

As I understand it the ORDER BY in the subquery should just return the same number of rows but in a different order. Is that correct?

Thanks,
Steve

[gpl]

Then this is what you want

SELECT Column1, Column2
FROM Table1
WHERE 
Table1.Column1 IN (
SELECT TOP (5) Column1
FROM Table2 
ORDER BY Column1 
)
ORDER BY NEWID()
 

Moving the ORDER BY NEWID() to the outside of your where subselect means the top 5 values will be supplied in a random order

[ck9663]

The IN clause does not care of the order. It just checks for existence. So even if your sub-query returns the top 5 in whatever order the the IN clause will always return true and your outer query should return the same result set.

If your requirement is for the outer query to return random records, then GPL's code will do.

Happy Coding!!!

~~ CK

[NeoPa]

I have no idea why you would get a differnt number of rows Steve. It is possible for the TOP predicate to return more rows than the value specified, if there are a number of records that have matching values around the nth record (in whichever sequence is used). I'm guessing this is not the case here (from your description of the problem).

Does this even matter now? Gpl's solution should provide exactly what you require anyway. Let us know how it works for you.