Extract Random Sample of Transaction Records for Specific Clients

[garlanj]

I have a table which contains about 43,500 client transaction records for about 2000 different clients. I need to develop some code to extract a random sample of 3 transaction records for each client. Ideally I'd also like to be able to select the clients I sample from - ie for clients 1,4,5,6,22,31 and 32 randomly select 3 transactions - any help would be gratefully received!

 

[lupins46]

Do you mean you want a true random selection or do you just mean 'any 3'?

 

[garlanj]

It has to satisfy our auditors - so it should be a random sample of 3 transactions for each selected client (ie it can't be the first 3 or the last 3 etc, etc)

 

[PHV]

Use a Top 3 correlated subquery with a sorting clause like this:
ORDER BY Rnd(SomeNumericField)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244