Here's an interesting twist:

SELECT TOP (100) ABS(CHECKSUM(NEWID())) % 100

FROM dbo.syscolumns

WHERE ABS(CHECKSUM(NEWID())) % 100 BETWEEN 1 AND 5

Notice that the values returned are way outside the range 1..5 since the NEWID() function generates a new unique identifier every time it's invoked, in both the SELECT clause and the WHERE clause of the same statement. 🙂