mercredi 7 septembre 2022

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

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.




Aucun commentaire:

Enregistrer un commentaire