Posted by Roy Harvey on 12/01/06 14:28

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())

Roy Harvey
Beacon Falls, CT

On 1 Dec 2006 04:44:27 -0800, FrankEBailey@gmail.com wrote:

>I'm not sure if this is a completely dumb question, but please humor me
>:) I have a table of records, called Records, each of which has a
>Category_ID that places it in a specific category; the details of the
>categories are stored in another table called Category. What I need to
>do is retrieve a recordset that contains one record from each category,
>but where the records that are retrieved are random. I know how to
>retrieve one or more random records using "order by NewID()", but have
>not been able to work out how to get one random record from each
>category.
>
>Any assistance in this puzzler will be HUGELY appreciated!
>
>Thanks
>FEB

[Back to original message]