How to randomly select 10% of the rows from a table

Question

How to randomly select 10% of the rows from a table

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
October 8, 2019 at 9:16 pm

#3687181

I need to extract randomly to select 10% of the rows from a table. Kindly advise how to write the t-SQL.

Thanks.

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

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

Phil Parkin SSC Guru Points: 246693 More actions · Answer 1

Use NEWID() to force randomness.

SELECT TOP (10) PERCENT
       *
FROM sys.columns
ORDER BY NEWID();

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

thanks, Phil. Can I use random () to rewrite the same query?

Thanks.

Phil Parkin SSC Guru Points: 246693 More actions · Answer 3

SQL_Hunt wrote:

thanks, Phil. Can I use random () to rewrite the same query?

Please post the link to the T-SQL random() function that you intend to use.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

BrainDonor SSCoach Points: 19252 More actions · Answer 4

Phil's solution is definitely the more elegant but (for completeness) there is also a TABLESAMPLE clause.

Steve Hall
Linkedin
Blog Site