Tuesday, April 8, 2008

Select random rows from a MS-SQL Server table

Imagine you have a Product table, where you need to get a list of say 5 random products. This is how you can do it:
select top 5 *
from Product
order by newid();
So what actually happens is that newid() returns a new GUID/unique identifier for every row in the result set, it then sorts based on the GUID value and then eventually selects the 5 top most rows.