1

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Piston
  • 95
  • 1
  • 12
  • Why not just get the result from the app and make it random in your program logic? – juergen d Feb 07 '17 at 11:47
  • Because the program basically shows the table with all rows, and only allows some basic filters (sorting and filtering by content) and then an option to add a where clausule. – Piston Feb 07 '17 at 11:52

2 Answers2

3

Try this:

SELECT *
FROM Test 
WHERE Id IN (SELECT TOP 10 Id FROM Test ORDER BY NewId())
Steve Ford
  • 7,433
  • 19
  • 40
1

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())

Randolph
  • 302
  • 3
  • 12