Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

random records Expand / Collapse
Author
Message
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: Thursday, August 1, 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


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:09 AM
Points: 314, Visits: 2,530
Use Top clause without order by clause.

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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:33 AM
Points: 2,616, Visits: 4,720
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
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: Yesterday @ 2:08 AM
Points: 563, Visits: 997
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

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 11, 2014 10:54 AM
Points: 292, Visits: 1,620
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
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 @ 10:24 PM
Points: 3,997, Visits: 6,049
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

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 @ 10:24 PM
Points: 3,997, Visits: 6,049
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 (119 views, 1.62 KB)
Post #1036679
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 @ 2:50 PM
Points: 36,711, Visits: 31,159
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1036822
Posted Saturday, March 1, 2014 10:34 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 8:39 AM
Points: 605, Visits: 1,406
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.


it helps to talk it out
Post #1546688
Posted Sunday, March 2, 2014 9:49 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: Monday, July 14, 2014 2:06 PM
Points: 3,854, Visits: 7,130
Would this work?
DECLARE @Rand INT
SET @Rand = (RAND() * 20) + 10

SELECT 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
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse