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

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) ``````
``` By : Gordon Linoff ```
``` This video can help you solving your question :) By: admin ```
``` Related Questions SQL Server: How can I copy data from one table to another table without the characters 'e+' being added to the results Get size of a database that is hosted on a linked server? 3d spatial objects in sql server (adsbygoogle = window.adsbygoogle || []).push({}); Recursive query issues in SQL Server With a foreign key, can I reference a fixed value in a composite primary key? Stored procedure EXEC vs sp_executesql difference? Select minimum value in group Union of two boolean Rows with the same tag how to create a table structure from column values of another table in MSSQL IF statement in SQL WHERE clause Failed to retrieve tables from database in SQL Server I dont know sql syntax for my table [closed] Try_Convert for SQL Server 2008 R2 SQL table design strategy for large amounts of location data with lots of inserts What's the best version neutral method for deploying a SQL Server database? Is it possible to create this kind of query in SQL (SQL Server 2012)? [duplicate] What are all the characters that should be escaped from a string to avoid SQL injection attacks in SQL Server? How to split string in 3 pieces in SQL Server Count number of occurrences in a bit column in sql ```
``` ```
``` About Us    Contact Us    Legal    feedback    Copyright © 2015 - All Rights Reserved - www.4answered.com ```