To select random-looking n Rows, I can sort by NEWID and use TOP. A developer asked about random selection during a health check.
The request also required avoiding a repeated selection on later runs. Shuffling and tracking prior selections are different requirements. The ten-row example below demonstrates the first.
-- Run in an installed AdventureWorks sample database.
SELECT TOP (10) ProductID, Name
FROM Production.Product
ORDER BY NEWID();Each row receives a generated value for sorting. TOP returns ten rows if at least ten exist. Later runs usually select a different set, but overlap or an identical set is possible. Separate executions don’t guarantee sampling without replacement.
The query can scan and sort substantial data. I use it for a modest table or a lab and select only needed columns. TABLESAMPLE has different page-based behavior. It does not promise exactly n rows.
Store and enforce selection state if a row must never be selected again. That requirement differs from shuffling output. NEWID ordering is not a cryptographic random-number service.
Related reading
Shuffling output is not a record of past selections, it is a sampling technique with its own costs.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
9 Comments. Leave new
Hi
This can also work , if you can figure out how to generate a random number and pass it to the between clause then this can work well
WITH CTE_Random
AS
(SELECT ROW_NUMBER() OVER(ORDER BY ProductID) AS CNT, * FROM production.product )
SELECT * FROM CTE_Random WHERE cnt BETWEEN 300 AND 600
I tested the newid() solution on a large table , first run was 12 seconds and second run was 3 seconds
@julian , I did test your suggestion and always get the same results not random records, is this correct ? hmmm
Hi Carlos
This solution is not 100 % , you have to change the values in the between clause unless you can figure out a way to pass these values automatically, I just did not have the time to work that out
HI Carlos
Try this , you can change the Rand values to what ever you want
DECLARE @random1 int,
@random2 int
SET @random1 = (SELECT FLOOR(RAND()*(50-10+1))+10)
SET @random2 = (SELECT FLOOR(RAND()*(100-10+1))+50)
;
WITH CTE_Random
AS
(SELECT ROW_NUMBER() OVER(ORDER BY ProductID) AS CNT, * FROM production.product )
SELECT * FROM CTE_Random WHERE cnt between @random1 and @random2
How do we use this query in Query Shortcuts. By selecting table name and if I click the shortcut, then it should display n randow rows.
I love your site! You have helped me out so much over the years!!! Thanks!
Let’s say I have 40 records and I used Row_Number /partition by key column
1st set of the key column has 13 records — I need to pick 2 random record from this set
2nd set of the key column has 20 records — I need to pick 5 random record from this set
3rd set of the key column has 7 records — I need to pick 3 random record from this set
is it possible? if yes how?
What happens when it just happens that your random ID assignments are right in the middle of a GAP in the ID? You get nothing back.
I am aware this is 2018 thread.
Latest SQL Server allows you, using TABLESAMPLE option
SELECT * from dbo.FactTable TABLESAMPLE ( 10 PERCENT )
SELECT * from dbo.FactTable TABLESAMPLE ( 10 PERCENT ) REPEATABLE (25)
SELECT * from dbo.FactTable TABLESAMPLE ( 100 ROWS )
SELECT * from dbo.FactTable TABLESAMPLE ( 2000 ROWS ) REPEATABLE (25)
You can also add further conditions using where clause. Say,
SELECT * from dbo.FactTable TABLESAMPLE ( 100 ROWS ) WHERE YEAR(UpdateDT) = 2019