Thursday, March 22, 2007

pick up random records from a table

Question:
How to pick up random records from a table?

Gabbar:

SELECT TOP 5
*
FROM
tblName
ORDER BY
right(convert(nvarchar(100),((row_number() over (order by ColumnName)) * rand())),3)

This will work in SQL 2005. To make it work in 2000, use any numeric column instead of row_number.

2 comments:

Anonymous said...

Try this.....


SELECT TOP 1 [colName] FROM [tableName]
ORDER BY NEWID()

Anonymous said...

Thanks for writing this.