If you use Microsoft SQL 2000, you’ve probably at one point had to pull random results. The built in rand() function simply doesn’t work. It gives you a random value the first time, and every time after it is the same it seems.
I found a new way to do this today after 20 pages of searching, so hopefully this will help someone else.
ORDER BY newid()
yup, it’s that simple.
SELECT
TOP 2
field1
,field2
FROM
TABLE
WHERE
field1 = 1
ORDER BY
newid()
To be noted, this will put a full table scan in place before it selects the top 2, so keep performance in mind.
Tags: Rand Function, SQL2000