SQL Server: random number in WHERE clause

user_1413856 0
Viewed 912

As far as I am aware, the only way to get a random value in a SELECT statement is by using the newid() function, as the random() function doesn’t generate new values for each row.

This leads to the following awkward construction to get a random number from, say 0 - 9:

abs(checksum(newid())) % 10

If I use this expression in the SELECT clause, it behaves as expected. However, if I try something like the following:

select *
from table
where abs(checksum(newid())) % 10>4;

I should have though that I would get roughly half the rows. Instead I get I get all or none of them. Apparently newid() is only evaluated once, instead of for each row.

The question is, how can I use a random number in the WHERE clause?

More

There is a similar question which asks for fixed number of rows at random. In the above example I could have used:

select top 50 percent from table order by newid();

which will get me what I am looking for.

The question remains, how can I use a random number in the WHERE clause. For example, is it possible to do something like this?

select *
from table
where code={random number};
select random sql-server
3 Answers

The function newid() is calculate only once in the WHERE clause, not row by row. The trick is to force it to run row by row.

Of course it is possible to include it in a SELECT clause, and, in turn, include that in a CTE or a subquery, as per the other answers.

Microsoft offer a solution here: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms189108(v=sql.105)?redirectedfrom=MSDN

The trick is to force newid() to recalculate by combining it with some row value. This is easily done in the checksum() function.

For example:

SELECT *
FROM table
WHERE abs(checksum(newid(),id)) % 10>4;
user_14138560
Related