How to Get Arbitrary 5 different rows from a table consisting of multiple rows?

Question

Post reply

How to Get Arbitrary 5 different rows from a table consisting of multiple rows?

mavericksql

Old Hand

Points: 302
More actions
April 13, 2011 at 11:54 pm

#249440

Hi All,
Few days back, one of my colleague asked me how to get 5 arbitrary different rows from a table which is consisting of multiple rows ?
Since SQL Server 2008 has NewId() function so i replied him you can use following script for your table
Select top 5 * from TableName
order by NewId();
I want to know - are there some other ways to know arbitrary rows from any database table ?

Viewing 4 posts - 1 through 3 (of 3 total)

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

srikant maurya Hall of Fame Points: 3293 More actions · Answer 1

You can use RAND() function to get the result

SELECT TOP 5

columnname,

*

FROM tablename

ORDER BY Rand()

abhijeetv SSCommitted Points: 1945 More actions · Answer 2

I guess this will return the same set of records every time...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

srikant maurya (4/14/2011)
You can use RAND() function to get the result
SELECT TOP 5
columnname,
*
FROM tablename
ORDER BY Rand()

That's equivalent to ORDER BY 'A constant value'. Rand returns the same value for all rows of a resultset.

You can use TableSample, along with TOP. That'll be semi-random, likely to be faster than the order by NewID, but it's rows off a random page, not truly random rows.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass