Select TOP 1 from records with weight taken into account


I already saw this post here (, 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, sum(tt.weight) AS cum_weight
            FROM        @Table t
            INNER JOIN  @Table tt ON <=
            GROUP BY tc
        ON =,
           (SELECT  SUM(weight) AS total_weight FROM @Table) tt,
           (SELECT  RAND() AS rnd) r
WHERE       r.rnd * tt.total_weight <= tc.cum_weight

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


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