Selecting a random set of 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

General SQL Server Forums

New to SQL Server Programming

Selecting a random set of rows

Author

Topic

Eddie M
Starting Member

14 Posts

Posted - 2011-10-07 : 09:34:21

I want to select about 100 random rows from a table of 1,000 rows.

I have tried this:


SELECT * FROM MyTable
WHERE RAND() BETWEEN 0.0 AND 0.1

But that didn't work. It seems that, in this context, RAND() will always return the same value. So the above code will always return either all the rows or no rows.

I also tried this, but with the same result:


SELECT TOP 10 PERCENT FROM MyTable
ORDER BY RAND()

I'd be pleased to hear of any other suggestions.

Eddie

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-10-07 : 09:43:06

SELECT TOP 100 * FROM myTable
ORDER BY NEWID()

Jim

Everyday I learn something that somebody else already knew

Eddie M
Starting Member

14 Posts

Posted - 2011-10-07 : 12:47:40

Jim,

Brilliant. Works perfectly. Thanks.

Eddie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources