Select N Random Rows from SQL Server Table

Select n random rows from SQL Server table

select top 10 percent * from [yourtable] order by newid()

In response to the "pure trash" comment concerning large tables: you could do it like this to improve performance.

select  * from [yourtable] where [yourPk] in 
(select top 10 percent [yourPk] from [yourtable] order by newid())

The cost of this will be the key scan of values plus the join cost, which on a large table with a small percentage selection should be reasonable.

How to randomly select rows in SQL?

SELECT TOP 5 Id, Name FROM customerNames
ORDER BY NEWID()

That said, everybody seems to come to this page for the more general answer to your question:

Selecting a random row in SQL

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Select a random row with IBM DB2

SELECT column, RAND() as IDX 
FROM table 
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Select a random record with Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

Select a random row with sqlite:

SELECT column FROM table 
ORDER BY RANDOM() LIMIT 1

How to select N random rows using pure SQL?

I don't know about pure ANSI, and it's not simple, but you can check out my answer to a similar question here:
Simple Random Samples from a Sql database

Select n random rows from table per group of codes

I suggest you look at NTILE for this.

Generate random sample from huge table, with conditions

Add a column to your table and populate it with random numbers.

ALTER TABLE `table` ADD COLUMN rando FLOAT DEFAULT NULL;
UPDATE `table` SET rando = RAND() WHERE rando IS NULL;

Then do

SELECT * 
  FROM `table` 
 WHERE rando > RAND() * 0.9
   AND condition = 0
 ORDER BY rando
 LIMIT 5000

Do it again for condition = 1 and Bob's your uncle. It will pull rows in random order starting from a random row.

A couple of notes:

• 0.9 is there to improve the chances you'll actually get 5000 rows and not some lesser number.
• You may have to add LIMIT 1000 to the UPDATE statement and run it a whole bunch of times to populate the complete rando column: trying to update all the rows in a big table can generate a huge transaction and swamp your server for a long time.
• If you need to generate another random sample, run the UPDATE or UPDATEs again.

How to request a random row in SQL?

See this post: SQL to Select a random row from a database table. It goes through methods for doing this in MySQL, PostgreSQL, Microsoft SQL Server, IBM DB2 and Oracle (the following is copied from that link):

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Select a random row with IBM DB2

SELECT column, RAND() as IDX 
FROM table 
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Select a random record with Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

Select random records with no duplicates

You may use ROW_NUMBER here with a random ordering:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Associate ORDER BY NEWID()) rn
    FROM yourTable
)

SELECT Associate, TrackingID
FROM cte
WHERE rn <= 3;

---

Related Topics