SELECT Random Number of Rows - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

SELECT Random Number of Rows

Author

Topic

ywb
Yak Posting Veteran

55 Posts

Posted - 2006-05-12 : 13:43:22

Hi,

How do I write an SQL statement that would select a random number, between 1 to 50, of rows of data from a table?

ywb.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-12 : 14:23:19

declare @i int
Set @i = convert(int, 50 * RAND() )
Set rowcount @i

Select * from 'UrTbl'

Srinika

ywb
Yak Posting Veteran

55 Posts

Posted - 2006-05-12 : 16:24:05

Hi Srinika,

Thanks, but it sometimes returns more than 50 rows...

This is the code I used to test against the Northwind database and sometimes it returns more than a thousand rows...

use northwind;
declare @i int
Set @i = convert(int, 5 * RAND() )
Set rowcount @i
Select * from [Order Details];

Why's that?

ywb.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-05-12 : 17:16:17

quote:
Originally posted by ywb

Hi Srinika,

Thanks, but it sometimes returns more than 50 rows...

This is the code I used to test against the Northwind database and sometimes it returns more than a thousand rows...

use northwind;
declare @i int
Set @i = convert(int, 5 * RAND() )
Set rowcount @i
Select * from [Order Details];

Why's that?

ywb.

That happens when @i is set to zero. This should fix the problem, becaue @i can only be 1 through 50.


use northwind;
declare @i int
Set @i = (abs(convert(int,convert(varbinary(20),newid())))%50)+1
select @i
Set rowcount @i
Select * from [Order Details];

CODO ERGO SUM

ywb
Yak Posting Veteran

55 Posts

Posted - 2006-05-15 : 13:19:16

Thanks, Michael. That works!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources