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: 246960 More actions · Answer 1

Use NEWID() to force randomness.

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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: 246960 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

BrainDonor SSCoach Points: 19254 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