Select Random Rows

[GaryMazzone]

Try this:

SELECT TOP 4 * FROM Product WHERE SubcategoryID = Value
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 4 top most rows.