Hi I have following query which helped to generate top 5 random accounts for each name.
Now, I need this task to be more difficult. I need to limit the max value for each name as 4.
Would you please use query only is possible. Please see an attached database for reference.
1. "Table 1" is the main table;
2. "Query1-generate random numbers" is the table with randomly generated accounts for each name. Sort in Query 1 is ensures that numbers are randomly selected and will be different during each run.
3. Table 2 is mostly needed for validation purposes.
4. As you can see that some name reflects 3 accounts, some 5, some 7 and some 8 accounts.
4.1 For each name I need to use same query "Top5-Query1" to randomly select top 4 numbers and limit max value to 4 for each name. Note: in real database the query "top5-Query1" reflects top value as percentage. Therefore I need to limit max value and it can be changed in future.