Random rows generator:

Question

Post reply

Random rows generator:

sudrav

SSC-Addicted

Points: 424
More actions
October 14, 2008 at 1:54 pm

#213999

I have a table of 100 records. I need a random of 2.5% of the reocrds. If I use Select Top (2.5) Percent , I get the first 2.5 % rows. I want a random of 2.5 rows. Any suggestions how to go about doing it ?

Viewing 8 posts - 1 through 7 (of 7 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

You can use TABLESAMPLE, like this:

SELECT *

FROM YourTable TABLESAMPLE (2.5 PERCENT)

However, BOL notes the following limitations of TABLESAMPLE:

You can use TABLESAMPLE to quickly return a sample from a large table when either of the following conditions is true:
The sample does not have to be a truly random sample at the level of individual rows.
Rows on individual pages of the table are not correlated with other rows on the same page.

If you really need a "random" sample then BOL suggests this:

Important:
If you really want a random sample of individual rows, modify your query to filter out rows randomly, instead of using TABLESAMPLE. For example, the following query uses the NEWID function to return approximately one percent of the rows of the Sales.SalesOrderDetail table:
SELECT * FROM Sales.SalesOrderDetail
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float)
/ CAST (0x7fffffff AS int)
The SalesOrderID column is included in the CHECKSUM expression so that NEWID() evaluates once per row to achieve sampling on a per-row basis. The expression CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float / CAST (0x7fffffff AS int) evaluates to a random float value between 0 and 1.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

KoldCoffee SSCoach Points: 18971 More actions · Answer 2

Hi RBarryYoung,

Can you please explain use of 0x7fffffff ?

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 3

KoldCoffee (3/1/2014)
Hi RBarryYoung,
Can you please explain use of 0x7fffffff ?

I don't know why they did that in Books Online except, maybe, that it's easier to remember than 2147483647, which is the maximum postive number that an INT can contain.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

KoldCoffee SSCoach Points: 18971 More actions · Answer 4

I don't know why they did that in Books Online except, maybe, that it's easier to remember than 2147483647, which is the maximum postive number that an INT can contain.

by which algorithm (trying to sound smart with that word) does 0x7fffffff convert to 2147483647? :unsure:

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 5

Shifting gears, I find the formula in Books Online to be inaccurate. The number of rows that it returns varies quite a bit. The following will always return the same number of rows although they will be random. Yeah... it takes longer than the BOL formula but it does return an accurate number of rows in random order. It's also a hell of a lot easier to understand for most people.

SELECT TOP 2.5 PERCENT * FROM Sales.SalesOrderDetail

ORDER BY NEWID()

;

And, yeah... I know this is a six year old thread but I'm answering the question from yesterday. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

KoldCoffee SSCoach Points: 18971 More actions · Answer 6

Well, I thank you very much for advising on the NEWID() function.

I went searching for the answer to my question in an existing thread and found these. I hope it's ok to revive an old thread to see if there's any update since time has passed.

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 7

KoldCoffee (3/2/2014)
I don't know why they did that in Books Online except, maybe, that it's easier to remember than 2147483647, which is the maximum postive number that an INT can contain.
by which algorithm (trying to sound smart with that word) does 0x7fffffff convert to 2147483647? :unsure:

It's not an algorithm. It's a simple Hex number once you remove the "0x" hex indicator. From right to left, each character represents a power of 16 (starting at 0) just like each character in a decimal number represents a power of 10.

To be sure about the right most character position is most whole numbering systems, any "base" value (16 for hex, 10 for decimal) raised to the 0 power is "1". That's why (for example), 5 hex = 5 dec.

An over simplified but effective primer on the subject can be found at the following URL (scroll down and hit the "Next" button on the screen):

http://www.wisc-online.com/Objects/ViewObject.aspx?ID=DIG1102

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)