Randomly Pick Specified Number of Rows?

Question

Post reply

Randomly Pick Specified Number of Rows?

ywb

SSC Enthusiast

Points: 162
More actions
July 4, 2008 at 2:40 pm

#72475

Hi,
How can I create a stored procedure that would take an integer @NumRows as input and randomly select that number of rows from the customer table?
SELECT TOP @NumRows *
FROM Northwind.dbo.Customers
ORDER BY NEWID();
Thanks,
ywb

Viewing 8 posts - 1 through 7 (of 7 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

That would work in 2005. In SQL 2000, try rowcount.

SET ROWCOUNT @NumRows

SELECT *

FROM Northwind.dbo.Customers

ORDER BY NEWID();

SET ROWCOUNT 0

Please note, I don't have a 2000 server handy to test this. I think it'll work.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ywb SSC Enthusiast Points: 162 More actions · Answer 2

ywb

SSC Enthusiast

Points: 162

July 4, 2008 at 2:59 pm

#838443

It does. Thanks!

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

ywb (7/4/2008)
It does. Thanks!

Two way street... I'm always curious about these type of things...

Why do you need to do this, please?

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

Melville SSCommitted Points: 1985 More actions · Answer 4

I seem to remember from the dim and distant that the set rowcount @somevar to limit the rows returned starts to break down on complex queries, but is fine for single tables. Obviously BOL suggests using 'select top 100 *' - but you can't use a variable instead of the 100 in Sql2000.

As to Mr. Moden's 'why' - I think that the answer is that sometimes you want a specified but user variable number of rows returned - precisely the cause of the introduction of set top @somevar in Sql2005.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

Melville (7/5/2008)
As to Mr. Moden's 'why' - I think that the answer is that sometimes you want a specified but user variable number of rows returned -

Heh... lordy... I know that... I want to know why the user wants to return random rows to begin with.

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

ywb SSC Enthusiast Points: 162 More actions · Answer 6

Hi Jeff, just fyi, it's for a marketing application. We send promotion info to our users on a regular basis. Different promos are available to different number of users, and we need a stored proc to randomly select the specified number of users.

That's why...

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

ywb (7/6/2008)
Hi Jeff, just fyi, it's for a marketing application. We send promotion info to our users on a regular basis. Different promos are available to different number of users, and we need a stored proc to randomly select the specified number of users.
That's why...

...and that's probably one of the few good reasons there is to do such a thing. Thank you very much for the feedback. 🙂

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