Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


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

Home » SQL Server 2008 » T-SQL (SS2K8) » random records
Add to briefcase

random records Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
ranadip.dey
Posted Thursday, December 16, 2010 12:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 18, 2013 1:23 AM
Points: 3, Visits: 35
How to select random (10 or 20) records from table?
Post #1035673
Sachin Nandanwar
Posted Thursday, December 16, 2010 1:38 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:14 AM
Points: 238, Visits: 2,341
Use Top clause without order by clause.

Select top 10 columnname from yourtable.
Post #1035684
Kingston Dhasian
Posted Thursday, December 16, 2010 2:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 2,231, Visits: 4,202
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 Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1035692
grahamc
Posted Thursday, December 16, 2010 3:42 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:21 AM
Points: 563, Visits: 943
What about the TABLESAMPLE clause?

EG.
  SELECT *
  FROM tableX
  TABLESAMPLE (10 percent)

More info here
Post #1035731
Lamprey13
Posted Thursday, December 16, 2010 4:17 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 09, 2013 4:22 PM
Points: 285, Visits: 1,575
Here is another method I've seen/used that is pretty similar to the ORDER BY NEWID(), but much faster:
SELECT TOP 10 * FROM MyTable
WHERE 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
The Dixie Flatline
Posted Thursday, December 16, 2010 5:10 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:05 PM
Points: 3,789, Visits: 5,547
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
The Dixie Flatline
Posted Friday, December 17, 2010 11:53 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:05 PM
Points: 3,789, Visits: 5,547
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 (74 views, 1.62 KB)
Post #1036679
Jeff Moden
Posted Friday, December 17, 2010 6:57 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:14 AM
Points: 32,910, Visits: 26,802
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1036822
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.