query for random data but grouped

Question

Post reply

query for random data but grouped

SQL_NuB

Hall of Fame

Points: 3666
More actions
October 16, 2009 at 11:19 am

#134517

I currently have a query that is pulling random records from my table, 10% of total 25 rows max. Now I need to take it to the next level and not sure how to.
I need to now pull random records for each type, meaning if I have 500 records in my table with types of [chevy, mercedes, dodge, ford) I need to pull records for each type but no more then 10% of the total and no more then 25 rows.
so if my total is 500, I have the following:
130 of ford - get 10% of total
200 of Chevy - 10% of total
100 for Mercedes - 10% of total
and 70 of dodge - 10% of total
with having the overall total 10% of overall total and no more then 25 rows
how can I pull records for each make BUT NOT exceed the 10% threshold and 25 rows overall?
This is my current random query:
SET NOCOUNT ON;
SET ROWCOUNT 25
DECLARE @selected TABLE (clmId int)
INSERT INTO @selected
SELECT TOP 10 percent clmId
FROM datafeed
where make = @make and isSelected is Null and DateClosed is NOT NULL and [quarter] = @Qtr and [Year] = @Year
ORDER BY NEWID()
UPDATE SalesFeed
SET isSelected = 1
FROM SalesFeed AS F
INNER JOIN @selected AS S
ON F.clmId = S.clmId
SELECT *FROM SalesFeed AS F
INNER JOIN @selected AS S ON F.clmId = S.clmId
any help is greatly appreciated

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

briangillock SSC Rookie Points: 32 More actions · Answer 1

I'm not all that knowledgable in SQL, but I think this is where I'd start.

IF...ELSE based on count greater than 250?

IF Boolean_expression

{ sql_statement | statement_block }

[ ELSE

{ sql_statement | statement_block } ]

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

First, because it's such an unusual requirement, I guess I'd like to know the business reason behind this, if you don't mind. Thanks.

Second, I have a question. What will be the distribution of the models in the finals 25 rows? Should it be as even as possible or should that be random as well?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL_NuB Hall of Fame Points: 3666 More actions · Answer 3

Its for audit purposes, and yes and no, it should be even, but not exactly, just as long as there are records from each group by no more then 10% of the overall total (25 rows max)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

Sorry... I just haven't gotten to this one, yet. Do you still need help on it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL_NuB Hall of Fame Points: 3666 More actions · Answer 5

SQL_NuB

Hall of Fame

Points: 3666

October 23, 2009 at 5:08 am

#1069910

Yes