select top records in multiple groups

[Guest]

how can i randomly select ten records in each of ten distinct groups within a
table ?

 

[[MVP] S.Clark]

I use VBA code when I need to do this. See the Rnd() function in the help
file.

 

[Michel Walsh]

Hi,


I would first fill a temporary table with an additional field for the random
number:


SELECT id, grp, Rnd(id) as randomValue INTO temp FROM myOriginalTable


I assumed the field id is numerical.

Once the randomness has been "fixed", you can then select the top 10 for
each group:


SELECT id, grp
FROM temp As a
WHERE a.randomValue IN( SELECT TOP 10 b.randomValue
FROM temp as b
WHERE b.grp = a.grp
ORDER BY b.randomValue DESC)
ORDER BY grp, id





That last query may be slow, so instead, you may try to "rank" the random
value, by group, and take those having a rank <= 10:


SELECT a.id, a.grp
FROM temp as a INNER JOIN temp as b
ON a.grp=b.grp AND a.randomValue >= b.randomValue
GROUP BY a.id, a.grp
HAVING COUNT(*) <= 10



Hoping it may help,
Vanderghast, Access MVP