Categories
Programming

Rand() isn’t Random

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.