0

I have a table with 1.3 million rows.

How do I just select a random 100 rows?

My table name is [March Value Mailer Offer Audience] and the attribute in the table is individual_id

I just want it to randomly pick 100 rows for a test that I am running

Ciccotelli412
  • 51
  • 6
  • 1
    Possible duplicate of [Select n random rows from SQL Server table](https://stackoverflow.com/questions/848872/select-n-random-rows-from-sql-server-table) – GMB Feb 26 '19 at 19:52

3 Answers3

2

The NEWID technique that people suggested here may be a performance challenge, as each row of your 1.3M rows will be assigned a NEWID, all will be sorted, and only then your top 100 will be returned.

SQL Server offers a much more efficient way to sample rows, using the table hint TABLESAMPLE. For example,

select * from [March Value Mailer Offer Audience] TABLESAMPLE(100 ROWS);

See more information here

HTH

SQLRaptor
  • 671
  • 4
  • 14
  • That sounded awesome until I read the docs and found that you aren't guaranteed to get the requested number of rows back, and the less you ask for the less likely you'll get the correct number back. – Dale K Feb 26 '19 at 20:15
  • @DaleBurrell If you care about getting the exact number of rows back, you can use TABLESAMPLE(150), and select the top 100 from those. It will still be orders of magnitude more efficient than assigning random values to each row in the source table and sorting... – SQLRaptor Feb 26 '19 at 20:25
  • Sure - I just thought that was worth you clarifying in your answer for the OP. – Dale K Feb 26 '19 at 20:26
1

An expensive method is:

select top (100) t.*
from t
order by newid();

If you want arbitrary rows, you can just do:

select top (100) t.*
from t;

Or, a convenient solution that is both random and should be quicker:

select top (100) t.*
from t
where rand(checksum(newid())) < 0.001   -- about 1 in 1000 rows
order by newid();

This pares down the rows to a random subset of about 1300 (0.1%) rows and then randomly chooses among them.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Use newid() :

select top (100) a.*
from [March Value Mailer Offer Audience] a
order by newid();

However, only top (n) would do what you want without any ordering :

select top (100) a.*
from [March Value Mailer Offer Audience] a;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52