September 11, 2005 at 3:04 pm
Dear All
I need help..
i have table imclude ID and Points fields .. i nned Like:
ID Points
1 10
2 2
3 5
4 10
5 5
6 10
i need to select random 4 records where sum points = 30
any one can help me ??
September 16, 2005 at 8:37 am
It can be done in a couple of ways...I am listing one of the ways to do this:
--Creating the actual table
CREATE TABLE TESTCASE (ID INT IDENTITY(1, 1) PRIMARY KEY, POINTS INT)
INSERT INTO TESTCASE (POINTS) VALUES (10)
INSERT INTO TESTCASE (POINTS) VALUES (5)
INSERT INTO TESTCASE (POINTS) VALUES (5)
INSERT INTO TESTCASE (POINTS) VALUES (10)
INSERT INTO TESTCASE (POINTS) VALUES (5)
INSERT INTO TESTCASE (POINTS) VALUES (10)
INSERT INTO TESTCASE (POINTS) VALUES (10)
INSERT INTO TESTCASE (POINTS) VALUES (10)
INSERT INTO TESTCASE (POINTS) VALUES (10)
INSERT INTO TESTCASE (POINTS) VALUES (3)
INSERT INTO TESTCASE (POINTS) VALUES (4)
INSERT INTO TESTCASE (POINTS) VALUES (3)
INSERT INTO TESTCASE (POINTS) VALUES (5)
INSERT INTO TESTCASE (POINTS) VALUES (1)
GO
--DROP TABLE #TEMP
--GO
--Creating the temp table
CREATE TABLE #TEMP (ROW_NUM INT IDENTITY(1,1), ID INT, POINTS INT)
GO
--Inserting records into the temp table randomly
INSERT INTO #TEMP (ID, POINTS)
SELECT ID, POINTS
FROM TESTCASE
ORDER BY RAND(@@IDLE%ID +(DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()))
GO
--Actual query that limits based on the running total
SELECT ID FROM
(
SELECT ID, POINTS, (
SELECT SUM(POINTS)
FROM #TEMP
WHERE ROW_NUM <= X.ROW_NUM) RUNNING_TOTAL
FROM #TEMP X ) DT
WHERE RUNNING_TOTAL <= 30
GO
You can very easily encapsulate all of this in a UDF (if UDF, use the table variable) or a stored procedure. If you just want to test the script above, make sure that you are either truncating the local #temp table on each iteration if you are doing it in the same session or drop and re-create the local #temp table - if you do it within a procedure, the scope of the local temp table is only for that procedure definition.
Hth
September 16, 2005 at 11:54 am
One thing that I wanted to mention about the approach from above - since it is based on the running total after the randomization is done - it looks for the exact match of 30 or less..so, in other words if you have records (after randomization) like:
ID POINTS RUNNING_TOTAL
6 10 10
5 17 27
11 5 32
Then, it will give you the ID values of 6 and 5 only because there is no exact match for 30. If you are looking to isolate the different combinations first that could result into a summation of 30 and then randomize those, then you will need to do that in a UDF since you will need to consider the different permutations and combinations that could lead to the sum of 30.
September 16, 2005 at 2:48 pm
I've seen this type of question before and composed an answer -- it was quite challenging for me the first time. This was originally in
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9924
There was another similar solution there from somebody else. NOTE -- this one was for four random values adding up to 45. Could be adapted to your case. Temp table is not required if you use a derived table. Here's the code:
create table testTable ( id int not null identity(1,1) primary key, points int ) insert testtable select 5 union all select 10 union all select 10 union all select 2 union all select 15 union all select 20 union all select 30 union all select 20 select id, points from testtable join ( select top 1 t1.id as id1, t2.id as id2, t3.id as id3, t4.id as id4 from testtable t1 inner join testtable t2 on t1.id t2.id inner join testtable t3 on t3.id t2.id and t3.id t1.id inner join testtable t4 on t4.id t3.id and t4.id t2.id and t4.id t1.id where 45 = t1.points + t2.points + t3.points + t4.points order by newid() ) allCombs on id = id1 or id = id2 or id = id3 or id = id4
September 19, 2005 at 8:47 am
Very nice solution Merrill.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply