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.