SQL Server Performance Forum – Threads Archive
Total Sum with random Records Question !!
Dear AllI’m new member in this great forum ..
i need help if any one can could help me
i have table include ID and points fileds like this ID Points
—– ———
101 5
102 10
103 10
110 2
111 15
125 20
135 30
145 20
i need to write query to select random record with creteria of (Number of records = 4 and Sum of Points = 45 )
Example :
ID Points
—– ———
101 5
125 20
145 20
———————————-
Total = 45 any one can help me to write query like that in SQL Server 2000
any one can help me
Very interesting puzzle. How about this? 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
thanks
i wll try it and inform you
thank you very much
Whatever reason can you have for this query? Not totally random (what ever is?) but this may get you started – at least in tabular form, sorry but you need a cross-tab representation of the resulting data to get it presented in successive rows: SELECT T1.ID, T1.Points, T2.ID, T2.Points, T3.ID, T3.Points, T4.ID, T4.Points,
(T1.Points + T2.Points + T3.Points + T4.Points) AS CheckSum
FROM Tbl T1
INNER JOIN Tbl T2
INNER JOIN Tbl T3
INNER JOIN Tbl T4
ON (T1.Points + T2.Points + T3.Points + T4.Points) = 45
AND T1.ID <> T2.ID AND T1.ID <> T3.ID AND T1.ID <> T4.ID
AND T2.ID <> T3.ID AND T2.ID <> T4.ID
AND T3.ID <> T4.ID Depending on the number of rows in your source data, this query may take some time to finish …
Dear merrillaldrich
it’s great but what if we wanna number of records to dynamic .,,,
some times
5 record and points = 45
10 record and points = 60
20 record and points = 85 so number of records no fixed ………
Random and dynamic … that’s tougher! What is this, a homework assignment <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />?<br /><br />Is there an upper limit to the number of records?
Noooo lol actuly im working on online exms system using ASP ,,,.
i need to generate questions for exam ,the number of question depend on the number of exam Question
so when you create an exam you should assign number of questions and the total Exam Points
after that you shold quesru Question Bank table to generate question depending on Number of questions and total points para.
..
Awlright, dell_10, you asked for it. This one kinda made my head hurt! — Dynamic number of cols 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
join ( select id, points from testtable
union select null as id, null as points ) t2
on t1.id < t2.id or t2.id is null
join ( select id, points from testtable
union select null as id, null as points ) t3
on t2.id < t3.id or t3.id is null
join ( select id, points from testtable
union select null as id, null as points ) t4
on t3.id < t4.id or t4.id is null where 45 = t1.points
+ coalesce(t2.points, 0)
+ coalesce(t3.points, 0)
+ coalesce(t4.points, 0) order by newid()
) allCombs
on id = id1 or id = id2 or id = id3 or id = id4
merrillaldrich you are great
thank you very very very much .. thank you very very very much .. thank you very very very much .. thank you very very very much .. thank you very very very much .. thank you very very very much ..
Aw shucks … don’t make my head swell up now …<br /><br /><img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />
]]>