SELECT TOP and Random results

[longyp]

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

[brucevde]

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..

[brucevde]

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)

[longyp]

Thanks...

This will do the trick...