10 Random records, 1 from each category

[FrankEBailey@gmail.com]

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

[Roy Harvey]

Re: 10 Random records, 1 from each category

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@gm ail.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

[FrankEBailey]

Re: 10 Random records, 1 from each category

Hi Roy,

On Dec 1, 4:28 pm, Roy Harvey <roy_har...@sne t.netwrote:

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?

Thanks
FEB

[Roy Harvey]

Re: 10 Random records, 1 from each category

Sorry, I'm out of ideas.

I am curious though. I assume you ran the query, and you say it
returned a single row. I expected it to return one row for each
Category. What exactly did it return?

Roy

On 1 Dec 2006 11:01:00 -0800, "FrankEBail ey" <FrankEBailey@g mail.com>
wrote:

>Hi Roy,
>
>On Dec 1, 4:28 pm, Roy Harvey <roy_har...@sne t.netwrote:

>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?
>
>Thanks
>FEB

[--CELKO--]

Re: 10 Random records, 1 from each category

An attribute is an identifier or a category, but not both by
definition. I assume that records means phonographic records so we
would use the RIAA number as the key - hey, no DDL or specs and I am
free to assume industry standards and make the names meaningful.

SELECT R1.record_categ ory, R1.riaa_nbr
FROM Records AS R1
WHERE R1.riaa_nbr =
(SELECT TOP 1 R2.riaa_nbr
FROM Records AS R2
WHERE R1.record_categ ory = R2.record_categ ory
ORDER BY NEWID());

Roy just got the corelation names wrong. This is also untested code.

[FrankEBailey]

Re: 10 Random records, 1 from each category

Hi Roy,

On Dec 1, 4:28 pm, Roy Harvey <roy_har...@sne t.netwrote:

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?

Thanks
FEB

[Erland Sommarskog]

Re: 10 Random records, 1 from each category

FrankEBailey (FrankEBailey@g mail.com) writes:

On Dec 1, 4:28 pm, Roy Harvey <roy_har...@sne t.netwrote:

>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@sommarsk og.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

[Madhivanan]

Re: 10 Random records, 1 from each category


FrankEBailey@gm ail.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

Also Refer point 2

http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan