CODEWITHSUNDEEP
sql-serverrandom
Piston
Piston

Reputation: 95

SQL Server random rows on where clause

I need to select 10 random rows from a table, but it has to be done in the where clause, because the query is executed using another aplication that only allows to modify this part.

I searched for a lot of solutions (select top 10, RAND(), ORDER BY NEWID(), ...), but none work in the where clause.

There an option to do that? or some kind of workaround?

Upvotes: 1

Views: 349

Answers (2)

Steve Ford
Steve Ford

Reputation: 7753

Try this:

SELECT *
FROM Test 
WHERE Id IN (SELECT TOP 10 Id FROM Test ORDER BY NewId())

Upvotes: 3

Randolph
Randolph

Reputation: 302

If your table has a unique column you can do something like :

SELECT * FROM TABLE WHERE PRIMARYCOLUMN IN (SELECT TOP(10) PRIMARYCOLUMN FROM TABLE ORDER BY NEWID())

Upvotes: 1

Related Questions