Select TOP 1 from records with weight taken into account

Question!

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()?

Table Stuff

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()

By : Patriotec


Answers

You could accomplish this by sampling from the cumulative sum, instead of from the records. The idea is to take a cumulative sum of the weights, then to take a random value up to the maximum weight, and finally to see which record has a cumulative sum around that random value. The SQL looks like:

select top 1 t.*
from (select t.*, cumulative_sum(weight) as cumweight,
             sum(weight) over (partition by NULL) as totalweight
      from t
     ) t
where rand()*(totalweight 1) 


This video can help you solving your question :)
By: admin