I already saw this post here (http://stackoverflow.com/questions/1398113/sql-select-one-row-randomly-but-taking-into-account-a-weight), but couldnt work it out. Where do I put the 'Stuff' table? Why don't they use NEWID() instead of RND()?
id item weight location 1 ball 1 Wyoming 2 cup 2 Alaska 3 sock 1 Idaho 4 car 3 Miami 5 hot girl 5 Brazil
Now according to that article referenced above I'm supposed to do this
SELECT TOP 1 t.* FROM @Table t INNER JOIN (SELECT t.id, sum(tt.weight) AS cum_weight FROM @Table t INNER JOIN @Table tt ON tt.id <= t.id GROUP BY t.id) tc ON tc.id = t.id, (SELECT SUM(weight) AS total_weight FROM @Table) tt, (SELECT RAND() AS rnd) r WHERE r.rnd * tt.total_weight <= tc.cum_weight ORDER BY t.id ASC
I want to do the above, but in this fashion:
SELECT TOP (1) from stuff WHERE blahblahblah AND (location='Brazil' OR location='Wyoming' OR location='Brazil') AND (weight <= cum_weight) ORDER BY NEWID()
I'm only guessing I can use NEWID() and not compelled to use RND()