Posted by Erland Sommarskog on 12/03/06 12:52

FrankEBailey (FrankEBailey@gmail.com) writes:
> On Dec 1, 4:28 pm, Roy Harvey <roy_har...@snet.net> wrote:
>> Do the NewID bit in a subquery. This very untested code should give
>> you some ideas.
>>
>> SELECT *
>> FROM Category as C
>> JOIN Records as R
>> ON C.Category_ID = R.Category_ID
>> WHERE R.PK =
>> (select TOP 1 R2.PK
>> from Record as R
>> where C.Category_ID = R2.Category_ID
>> order by NewID())
>
> This returns a single row, which is indeed random, but doesn't quite
> produce the result I need. Is there perhaps any way to return, say, 10
> records, each of which comes out of one of the ten available
> categories, but are randomised?

I adapted Roy's query to the Northwind database:

SELECT C.CategoryID, C.CategoryName, P.ProductName
FROM Categories as C
JOIN Products as P
ON C.CategoryID = P.CategoryID
WHERE P.ProductID =
(select TOP 1 P2.ProductID
from Products as P2
where C.CategoryID = P2.CategoryID
order by NewID())

and the result set appears to fit your description. I get 8 rows each
time I run it, and for each of the eight categories in Northwind I get
a random product.

If the query you composed from Roy's example does not give you the correct
result, maybe there was a mistake when you transformed the query to
your data model?

For this type of questions, it's always a good idea to post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

That way, people who answer your post can easily copy and paste into a
query tool to develop a tested solution.

It's also a good idea to tell which version of SQL Server you are using.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]