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()
Feel free to drop a few cents in the
tip jar if this post saved you time and money