Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 random records Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, December 16, 2010 12:42 AM
 Forum Newbie Group: General Forum Members Last Login: Thursday, August 01, 2013 7:01 AM Points: 3, Visits: 41
 How to select random (10 or 20) records from table?
Post #1035673
 Posted Thursday, December 16, 2010 1:38 AM
 SSC Veteran Group: General Forum Members Last Login: 2 days ago @ 11:10 PM Points: 240, Visits: 2,391
 Use Top clause without order by clause.Select top 10 columnname from yourtable.
Post #1035684
 Posted Thursday, December 16, 2010 2:05 AM
 SSCrazy Group: General Forum Members Last Login: Thursday, April 10, 2014 5:09 AM Points: 2,567, Visits: 4,654
 Sachin Nandanwar (12/16/2010)Use Top clause without order by clause.Select top 10 columnname from yourtable.This would mostly give the same result again and again. Use NEWID() in the ORDER BY Clause`SELECT TOP 10 * FROM YourTable ORDER BY NEWID()` Kingston DhasianHow to post data/code on a forum to get the best help - Jeff Modenhttp://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1035692
 Posted Thursday, December 16, 2010 3:42 AM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Wednesday, March 05, 2014 4:14 AM Points: 563, Visits: 971
 What about the TABLESAMPLE clause?EG.` SELECT * FROM tableX TABLESAMPLE (10 percent)`More info here
Post #1035731
 Posted Thursday, December 16, 2010 4:17 PM
 SSC Veteran Group: General Forum Members Last Login: Thursday, April 17, 2014 10:10 AM Points: 292, Visits: 1,615
 Here is another method I've seen/used that is pretty similar to the ORDER BY NEWID(), but much faster:`SELECT TOP 10 * FROM MyTableWHERE 0.01 >= CAST(CHECKSUM(NEWID(), SomeColumnName) & 0x7fffffff AS float) / CAST (0x7fffffff AS int) `NOTE: Repalce "SomeColumnName" with some columns or set of columns from your table, PKs work well.
Post #1036201
 Posted Thursday, December 16, 2010 5:10 PM
 Hall of Fame Group: General Forum Members Last Login: Monday, April 14, 2014 7:45 AM Points: 3,949, Visits: 5,944
 Using ORDER BY NEWID() with huge files is slow, because of the sort time.Generating TOP (X) WHERE (random number test) is fast, but tends to skew towards the front of the table, whatever that may be.If you have an indexed IDENTITY (ID) column in your primary table, I would generate a temp table of 10-20 distinct random numbers between min(ID) and max(ID) and join it to the primary. __________________________________________________Against stupidity the gods themselves contend in vain. -- Friedrich Schiller Stop, children, what's that sound? -- Stephen Stills
Post #1036229
 Posted Friday, December 17, 2010 11:53 AM
 Hall of Fame Group: General Forum Members Last Login: Monday, April 14, 2014 7:45 AM Points: 3,949, Visits: 5,944
Here is an example of pulling 20 random rows from a million row table.

Note to Jeff Moden: It may not be "Nasty Fast" but it's "Wicked Quick"

Had to make this an attachment. For some reason, SSC is blocking my posts.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills

Post Attachments
 Random Rows.txt (118 views, 1.62 KB)
Post #1036679
 Posted Friday, December 17, 2010 6:57 PM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 4:00 PM Points: 35,967, Visits: 30,258
 The Dixie Flatline (12/17/2010)Note to Jeff Moden: It may not be "Nasty Fast" but it's "Wicked Quick" Heh... too funny, Bob. --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." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1036822
 Posted Saturday, March 01, 2014 10:34 PM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Sunday, April 13, 2014 5:45 PM Points: 519, Visits: 1,209
 The Dixie Flatline (12/16/2010)Using ORDER BY NEWID() with huge files is slow, because of the sort time.Generating TOP (X) WHERE (random number test) is fast, but tends to skew towards the front of the table, whatever that may be.If you have an indexed IDENTITY (ID) column in your primary table, I would generate a temp table of 10-20 distinct random numbers between min(ID) and max(ID) and join it to the primary.I am in a place where I also need to select random rows. I like the NEWID() solution because it's simple. Can you say when a file is too huge to use it on? Is the temp table of random numbers between min(ID) and max(ID) etc....really the superior one? Thanks.
Post #1546688
 Posted Sunday, March 02, 2014 9:49 AM
 Hall of Fame Group: General Forum Members Last Login: 2 days ago @ 2:53 PM Points: 3,733, Visits: 7,072
 Would this work? `DECLARE @Rand INTSET @Rand = (RAND() * 20) + 10SELECT TOP @Rand FROM MYTable WHERE SomeIntColumn > @Rand` ______________________________________________________________________________"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1546707

 Permissions