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
