149.aspx

Selecting a random record from SQL

Today I needed a way to extract a random record from a small table. My first thought was to try something like:

SELECT TOP 1 OfferTitle, OfferDescription, OfferURL
FROM SpecialOffers WITH (NOLOCK)
ORDER BY Rand()

Close but no cigar. It doesn't work as SQL Server evaluates the Rand() function once and uses the same value for all the records.

The trick is to use NewID(). It's slower as the new ID is a GUID but for a few records it's no problem:

SELECT TOP 1 OfferTitle, OfferDescription, OfferURL
FROM SpecialOffers WITH (NOLOCK)
ORDER BY NewID()