SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Random rows generator:


Random rows generator:

Author
Message
sudrav
sudrav
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 49
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 ?
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35250 Visits: 9518
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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
KoldCoffee
KoldCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4183 Visits: 1905
Hi RBarryYoung,
Can you please explain use of 0x7fffffff ?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216402 Visits: 41986
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
KoldCoffee
KoldCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4183 Visits: 1905

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
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216402 Visits: 41986
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
KoldCoffee
KoldCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4183 Visits: 1905
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
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216402 Visits: 41986
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search