Trying to get multiple Random Rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Trying to get multiple Random Rows

Author

Topic

bduncs2001
Starting Member

17 Posts

Posted - 2009-01-09 : 15:09:59

Having difficulty getting multiple rows. Any clue how to fix?

Error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Sample Script:

SELECT [SKU],[Model No],[Brand Name],[Brand Create Date]
,(Select Top 2 Batch_ID from Brands where Batch_ID = '20070700')as July_2007
,(Select Top 2 Batch_ID from Brands where Batch_ID = '20070800')as August_2007
FROM Brands

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-01-09 : 15:17:32

These are obviously select 2 rows for each ONE row returned from the main select (FROM Brands).

,(Select Top 2 Batch_ID from Brands where Batch_ID = '20070700')as July_2007
,(Select Top 2 Batch_ID from Brands where Batch_ID = '20070800')as August_2007

You can do "Top 1"

What exactly are you trying to accomplish? Show some sample data as well as expected output for more help. The query doesn't make a lot of sense as written.

Terry

-- Procrastinate now!

bduncs2001
Starting Member

17 Posts

Posted - 2009-01-09 : 15:54:58

I've got over 300K records and I'm wanting to get a Random 10 rows where the rows have the same Batch_ID. After looking at the data again I would not need the the join data.

Data Looks like this:

SKU Model No Brand Name Brand Create Date Brand_ID
123A R16 RONCO 6/2/2007 0:00 20070600
123B B4 Kodak 7/18/2007 0:00 20070700
123C B6 Kodak 7/2/2007 0:00 20070700
123D LN40A630 Samsung 6/2/2007 0:00 20070600
123E LN46A630 Samsung 6/30/2007 0:00 20070600
123F BDP-S350 Sony 8/15/2007 0:00 20070800

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-09 : 16:08:09

I still can't tell what you want. But one way to get a random 10 rows is:

select top 10 <columnList>
from <table>
order by newid()

Now, you want the 10 rows to have the same "batch_id". Your sample data doesn't have a [batch_id] column ??? Perhaps you meant [Brand_ID] ? Do you want it for a specific [brand_id] or should that be random as well?

Be One with the Optimizer
TG

bduncs2001
Starting Member

17 Posts

Posted - 2009-01-09 : 16:10:26

There are 18 different "Brand_ID's" and I want to get a random 10 from each Brand_ID.

Thanks for the help and sorry for the missprint.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-09 : 17:40:03

Here's one way (this gets 2 random rows per brand_id):


create table #brands (
SKU varchar(4), [Model No] varchar(15), [Brand Name] varchar(20), [Brand Create Date] datetime, Brand_ID int)
insert #brands
select '123A', 'R16', 'RONCO', '6/2/2007 0:00', 20070600 union all
select '123B', 'B4', 'Kodak', '7/18/2007 0:00', 20070700 union all
select '123C', 'B6', 'Kodak', '7/2/2007 0:00', 20070700 union all
select '123D', 'LN40A630', 'Samsung', '6/2/2007 0:00', 20070600 union all
select '123E', 'LN46A630', 'Samsung', '6/30/2007 0:00', 20070600 union all
select '123F', 'BDP-S350', 'Sony', '8/15/2007 0:00', 20070800

select ca.SKU
       ,ca.[Model No]
       ,ca.[Brand Name]
       ,ca.[Brand Create Date]
       ,b.Brand_ID
from   (select brand_id from #Brands group by brand_id) b
cross apply (
       select top 2
              SKU
              ,[Model No]
              ,[Brand Name]
              ,[Brand Create Date]
              ,Brand_ID
       from   #Brands 
       where brand_id = b.brand_id 
       order by newid()
       ) ca

output:
SKU  Model No        Brand Name           Brand Create Date       Brand_ID
---- --------------- -------------------- ----------------------- -----------
123E LN46A630        Samsung              2007-06-30 00:00:00.000 20070600
123A R16             RONCO                2007-06-02 00:00:00.000 20070600
123B B4              Kodak                2007-07-18 00:00:00.000 20070700
123C B6              Kodak                2007-07-02 00:00:00.000 20070700
123F BDP-S350        Sony                 2007-08-15 00:00:00.000 20070800

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 03:01:38

quote:
Originally posted by bduncs2001

There are 18 different "Brand_ID's" and I want to get a random 10 from each Brand_ID.

Thanks for the help and sorry for the missprint.


SELECT SKU,
Model No,
Brand Name,
Brand,
[Create Date],
Brand_ID
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY Brand_ID ORDER BY NEWID()) AS Seq,*
FROM YourTable
)t
WHERE Seq<=10

bduncs2001
Starting Member

17 Posts

Posted - 2009-01-12 : 14:11:41

quote:
Originally posted by visakh16

quote:
Originally posted by bduncs2001

There are 18 different "Brand_ID's" and I want to get a random 10 from each Brand_ID.

Thanks for the help and sorry for the missprint.

SELECT SKU,
Model No,
Brand Name,
Brand,
[Create Date],
Brand_ID
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY Brand_ID ORDER BY NEWID()) AS Seq,*
FROM YourTable
)t
WHERE Seq<=10

Thanks...this worked out great

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-13 : 01:30:42

You can alos play with
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 02:48:45

quote:
Originally posted by bduncs2001

quote:
Originally posted by visakh16

quote:
Originally posted by bduncs2001

There are 18 different "Brand_ID's" and I want to get a random 10 from each Brand_ID.

Thanks for the help and sorry for the missprint.

SELECT SKU,
Model No,
Brand Name,
Brand,
[Create Date],
Brand_ID
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY Brand_ID ORDER BY NEWID()) AS Seq,*
FROM YourTable
)t
WHERE Seq<=10

Thanks...this worked out great

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources