Get random data from SQL Server but no repeated values

Question

I need to get 10 random rows from table at each time, but rows shall never repeat when I repeat the query.

But if I get all rows it will repeat again from one, like table has 20 rows, at first time I get 10 random rows, 2nd time I will need to get remaining 10 rows and at my 3rd query I need to get 10 rows randomly.

Currently my query for getting 10 rows randomly:

SELECT TOP 10 * 
FROM tablename
ORDER BY NEWID()

But MSDN suggest this query

SELECT TOp 10 * FROM Table1
  WHERE (ABS(CAST(
  (BINARY_CHECKSUM(*) *
  RAND()) as int)) % 100) < 10

For good performance. But this query not return constant rows. Could you please suggest something on this

score 2 · Answer 1 · answered Jul 26 '16 at 11:17

2

Since required outcome of your second query depends on the (random) outcome of the first query, the querying cannot be stateless. You'll need to store the state (info about the previous query/queries) somewhere, somehow.

The simplest solution would probably be storing the already-retrieved rows or their IDs in a temporary table and then querying ... where id not in (select id from temp_table) in the second query.

answered Jul 26 '16 at 11:17

Jiri Tousek

12,211
5
29
43

data is very large like we have 20000 rows and more then how can we store data into temp table and run again and again, It drop performance am i right? – Manikandan Jul 26 '16 at 11:32
1

Yes it will drop performance as you add records to the temporary table, but if you want the outcome you required your only option is to store the already selected rows somewhere, maybe a table with an index will provide enough performance for you case? – Doliveras Jul 26 '16 at 11:49
20k rows is not too much data. E.g. if your rows have an integer ID and you only store IDs of used rows, that could mean your temporary table can fit in perhaps 100kB, which can easily be cached in memory and accessed quickly even without indexes. – Jiri Tousek Jul 26 '16 at 12:04
Msdn suggested for this query SELECT * FROM Table1 WHERE (ABS(CAST( (BINARY_CHECKSUM(*) * RAND()) as int)) % 100) < 10 for performance Here i have simple question that is may i additional where conditional for here like id >10, where i need to add this where condition before Binary_checksum or after – Manikandan Jul 26 '16 at 12:51

Vladimir Baranov · Answer 2 · 2016-07-26T13:13:50.053

As Jiri Tousek said, each query that you run has to know what previous queries returned.

Instead of inserting the IDs of previously returned rows in a table and then checking that new result is not in that table yet, I'd simply add a column to the table with the random number that would define a new random order of rows.

You populate this column with random numbers once.

This will remember the random order of rows and make it stable, so all you need to remember between your queries is how many random rows you have requested so far. Then just fetch as many rows as needed starting from where you stopped in the previous query.

Add a column RandomNumber binary(8) to the table. You can choose a different size. 8 bytes should be enough.

Populate it with random numbers. Once.

UPDATE tablename
SET RandomNumber = CRYPT_GEN_RANDOM(8)

Create an index on RandomNumber column. Unique index. If it turns out that there are repeated random numbers (which is unlikely for 20,000 rows and random numbers 8 bytes long), then re-generate random numbers (run the UPDATE statement once again) until all of them are unique.

Request first 10 random rows:

SELECT TOP(10) *
FROM tablename
ORDER BY RandomNumber

As you process/use these 10 random rows remember the last used random number. The best way to do it depends on how you process these 10 random rows.

DECLARE @VarLastRandomNumber binary(8);
SET @VarLastRandomNumber = ... 
-- the random number from the last row returned by the previous query

Request next 10 random rows:

SELECT TOP(10) *
FROM tablename
WHERE RandomNumber > @VarLastRandomNumber
ORDER BY RandomNumber

Process them and remember the last used random number.

Repeat. As a bonus you can request different number of random rows on each iteration (it doesn't have to be 10 each time).

Viable way if a) the OP is able to modify the table itself, b) the table is stable throughout this process (no new or deleted rows), c) only one session performs this process at a time. The OP didn't provide any specific details so we don't really know. — Jiri Tousek, Jul 26 '16 at 13:20

score 0 · Answer 3 · answered Jul 26 '16 at 13:14

what I would do is have two new fields, SELECTED (int) and TimesSelected (integer) then

UPDATE tablename SET SELECTED = 0;

WITH CTE AS (SELECT TOP 10 * 
FROM tablename
ORDER BY TimesSelected  ASC, NEWID())
UPDATE CTE SET SELECTED = 1,  TimesSelected  = TimesSelected  + 1;

SELECT * from  tablename  WHERE SELECTED = 1;

so if you use that each time, once selected a record goes to the top of the pile, and records below it are selected randomly.

you might want to put an index on SELECTED and do

UPDATE tablename SET SELECTED = 0 WHERE SELECTED = 1;   -- for performance

yeah I think that'll work, once a record has been selected and become 1 it can't b reselected until all other records are 1's, when that happens some records will become 2 and then be ineligible until the others catch up — Cato, Jul 26 '16 at 13:19

Twinkles · Answer 4 · 2016-07-26T13:55:12.303

The most elegant solution, provided you do the consecutive queries within a certain amount of time, would be to use a cursor:

DECLARE rnd_cursor CURSOR FOR 
  SELECT col1, col2, ... 
  FROM tablename
  ORDER BY NEWID(); 

OPEN rnd_cursor;  
FETCH NEXT FROM rnd_cursor;  -- Repeat ten times

Keep the cursor open and just keep fetching rows as you need them. Close the cursor when you're done:

CLOSE rnd_cursor;  
DEALLOCATE rnd_cursor;

As for the second part of your question, once you fetched the last row, open a new cursor:

IF @@FETCH_STATUS <> 0
BEGIN
  CLOSE rnd_cursor;
  OPEN rnd_cursor;
END;

Get random data from SQL Server but no repeated values

4 Answers4