All Forums Database
ajaypratap 5 posts Joined 10/14
20 Mar 2016
Get n random records where n is stored in a table

I have a table TXN that has Transaction_ID and Store_nbr. Suppose also that I have a table SAMP that has Store_nbr and n_samples.
Suppose I want to take a sample of n_samples Transaction_IDs from TXN. I.e., I want the SAMP table to be able to drive how many transaction_id samples are taken from the TXN table. E.g.,

Transaction_ID Product_id Store_nbr
1 1 1
1 2 1
1 3 1
2 2 2
3 2 2


store_nbr n_samples
1 2
2 2
3 1

So how can I tell SQL: Give me 2 samples of transaction_Id for store 1, 2 samples of transaction_Id for store 2, and 1 sample of transaction_Id for store 3?
Also I have to make sure that If while sampleing we have picked transactioned ID 1 for store 1 then all the rows for product(in this case 1,2,3) should be picked.

You must sign in to leave a comment.