Get random value sets from table without using cursor or While loop

Pete Hesse 0
Viewed 17

I have a table with 5 columns: ID - int identity,col1,col2,col3,col4,(all 4 cols are varchar)

There are approx. 68,000 unique col1/col2 values. For each of these, there can be between 1 and approx. 214,000 unique col3/col4 values.

My task is to retrieve one random col3 and col4 (from the same row) for each of the unique col1/col2 values.

Is it possible to accomplish this without using a While loop or a cursor? I've done some research and know how to get random values (and the identity column helps with that), but the only way I can see to do this is to go thru the 68,000 unique col1/col2 values 1 by 1, and grab a random col3/col4 value from each.

Also, these row counts are for preliminary development/testing (collected from 4 previous months of data). When this goes live we will be going back 27 months. So obviously, we are talking about a massive amount of data.

I've seen some mentions of using CTE's, but have not been successful in finding an example or explanation.

Thanks for your help.

cursor common-table-expression while-loop random sql
1 Answers

I figured out a solution involving temp tables, ROW_NUMBER() over..., and RAND().

First, I selected the distinct col1 and col2 values into #temp1.

SELECT DISTINCT col1, col2
INTO #temp1
FROM sourceTable

Next, I selected the distinct col3 and col4 values for each col1/col2, along with a row number, and put in temp table #temp2:

SELECT t.COL1, t.COL2, a.col3, a.col4,
ROW_NUMBER() OVER ( 
    PARTITION BY t.col1, t.col2 
    ORDER BY t.col1, t.col2, a.col3, a.col4) as RowNumber
INTO #temp2
FROM #temp1 t
JOIN sourceTable a ON a.col1 = t.col1 AND a.col2 = t.col2
GROUP BY t.col1, t.col2, a.col3, a.col4
ORDER BY t.col1, t.col2, RowNumber

Then, I selected one of the rows at random from each set of col1/col2's into a 3rd temp table:

SELECT x.col1, x.col2,
    (SELECT TOP 1 y.RowNumber
     FROM #temp2 y
    WHERE y.col1 = x.col1
    AND y.col2 = x.col2
    AND y.RowNumber >= RAND() * 
        (SELECT MAX(z.RowNumber)
        FROM #temp2 z
        WHERE z.col1 = x.col1
        AND z.col2 = x.col2)) AS Random_RowNumber
INTO #temp3
FROM #temp1 x
ORDER BY x.col1, x.col2

Lastly, I join the tables to get the random rows:

SELECT t3.col1, t3.col2, t2.col3, t2.col4
FROM #temp3 t3
JOIN #temp2 t2 on t2.col1 = t3.col1 AND t2.col2 = t3.col2 AND t2.RowNumber = t3.Random_RowNumber
Pete Hesse0
Related