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.,
TXNTransaction_ID Product_id Store_nbr1 1 11 2 11 3 12 2 23 2 2
SAMPstore_nbr n_samples1 22 23 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.