SELECT TOP and Random results

Just discovered how to randomze results from a QUERY.

E.G

Select TOP 100 *
from tblCDs
ORDER BY Rnd(CDID)

Works a treat. Get me 100 random CDs from my collection.

BUT..what I would like to do is try and ensure the spread across the CDs is as even as possible;

i.e 10% Rock, 10% Dance, 10% Classical etc.

Any ideas would be great...

Thanks

You could use the union clause. You will need to adjust each Top 10 Percent line to get 100 records. Ie if you have 4 music types you would Select Top 25 Percent of each type.


Select TOP 10 PERCENT *
from tblCDs
Where MusicType = 'Rock'
ORDER BY Rnd(CDID)

Union All

Select TOP 10 PERCENT *
from tblCDs
Where MusicType = 'Classical'
ORDER BY Rnd(CDID)

etc..

oops! What was I thinking. The Top X Percent solution will not work. But doing a Union for each music type will work.

Select TOP 10 *
from tblCDs
Where MusicType = 'Rock'
ORDER BY Rnd(CDID)

Union All

Select TOP 10 *
from tblCDs
Where MusicType = 'Classical'
ORDER BY Rnd(CDID)

Thanks...

This will do the trick...