August 24, 2005 at 3:34 am
Hi,
I have table contains (id,point), i want select statement to retrive random 5 records from the table (select id,sum(point)) and sum of points must be equal 50.
any idea how to do this.
Thanks.
Best Regards,
Mahmoud Ahmed
mahmed
August 25, 2005 at 12:37 am
This is not random, but accomplishes the rest
SELECT TOP 5 id, SUM(point)
FROM MyTable
GROUP BY id
HAVING SUM(point) = 50
Andy
August 25, 2005 at 1:00 am
in my case this select will not work:
ex:
ID Point
1 25
2 10
3 35
4 30
5 10
6 15
7 10
8 15
9 10
10 10
--------------------------
if i run the query it will return 0 record.
mahmed
August 25, 2005 at 1:15 am
In your case, select id, sum(point) will not work.
I am wondering whether you want a logic to sum(point) regardless id.
From the data above, what I can see is you may have to sum(point) for id 2, 5, 7, 9 and 10 in order to get 50. Is that what you want?
Leo
August 25, 2005 at 1:39 am
yes that what i want.
mahmed
August 25, 2005 at 2:02 am
What values are possible in the point column? Any, or are there some limitations (like minimum = 5, maximum = 35, increment of 5)?
Anyway, this request goes IMHO against the basics of set-oriented approach and maybe it should not be done with SQL, but by some external application. What is the reason for such unusual requirement, what will you do with the five rows that would be returned? If we know more about the background, maybe we'll be able to find another way how to obtain the required result.
August 25, 2005 at 2:09 am
Hi,
this works, but it I am so sorry that it is really inelegant and needs a WHILE loop and a temp table.
I have used the RAND function to generate a number between 0 and the max ID value
I add numbers to the temporary table until the total = 50
I check when I add a number that the new total will be less than or equal to 50, and I also check that the new total plus the minimum of the values left can still give me a value of 50, otherwise I don't add the number.
This would probably best be written in a SP or UDF that returns a table.
You could put some extra logic in to make sure that you didn't go round the loop indefinitely
create table #pointer (id integer, point integer)
insert into pointy (id, point)
SELECT 1, 25
UNION SELECT 2, 10
UNION SELECT 3, 35
UNION SELECT 4, 30
UNION SELECT 5, 10
UNION SELECT 6, 15
UNION SELECT 7, 10
UNION SELECT 8, 15
UNION SELECT 9, 10
UNION SELECT 10, 10
create table #pointer (id integer, point integer)
delete from #pointer
while isnull((select sum(point) from #pointer),0)<50
BEGIN
insert into #pointer (id, point)
select id, point
from pointy,
--generate a random id number
(select round(rand()*(select max(id) from pointy),0) 'Random') pointless
where pointy.id=pointless.random
--new total must be <= 50
and ((select isnull(sum(point),0) from #pointer)+pointy.point)<=50
and (
--total must be 50 OR
((select isnull(sum(point),0) from #pointer)+pointy.point)=50
or
--minimum total+minimum value left must be <=50
((select min(point) from pointy where id not in (select id from #pointer))+
(select isnull(sum(point),0) from #pointer)+
pointy.point)<=50
)
END
select * from #pointer
David
If it ain't broke, don't fix it...
August 25, 2005 at 3:02 am
its working fine, thanks for your help.
mahmed
August 25, 2005 at 3:12 am
I agree. This is not a set based solution.
Because there are mutiple path a and sums to get 50, then ther will have to be lot of iterative logice
But: If you really did want to sum on id, say you had a table with
id, pointsdate, points
1, 3 Jan 2005, 40
1, 4 Jan 2005, 20
2, 2 Feb 05, 10
2, 4 Feb 05, 30
2, 5 Feb 05, 10
3, 3 Feb 05, 50
4, ...
etc
Then this will work, because it is a set based solution...
SELECT id, SUM(points) FROM table GROUP BY id HAVING SUM(points) = 50 ORDER BY NEWID()
Saw this on this forum years ago: ORDER BY NEWID(). Brilliant.
August 25, 2005 at 10:31 am
This is a set-based problem. There are just a lot of possible sets. So we have to find all of them and only return the 5 rows from the solution we picked. Fortunately, the problems specifies that each solution shall have 5 points, making the candidate sets easily created. In the 5-deep self join, we create all of the possible permutations. (If we wanted combinations, we'd change the < signs to <> ). Ordering by newid() puts the results in a random order for the TOP 1 to pick.
using a handy string function, we can pivot the pre-delimited candidate set into a vector.
There is currently only 1 solution, so try adding a few more points in the first select statement and you'll see that the code below will return different "pathways" each time.
I think I better GBTW now....
declare @pointy table(
id int,
point int)
insert @pointy (id, point)
SELECT 1, 25
UNION SELECT 2, 10
UNION SELECT 3, 35
UNION SELECT 4, 30
UNION SELECT 5, 10
UNION SELECT 6, 15
UNION SELECT 7, 10
UNION SELECT 8, 15
UNION SELECT 9, 10
UNION SELECT 10, 10
Select p.* from
@pointy p cross join
(Select top 1 ','
+ convert(varchar,p1.id) + ','
+ convert(varchar,p2.id) + ','
+ convert(varchar,p3.id) + ','
+ convert(varchar,p4.id) + ','
+ convert(varchar,p5.id) + ',' as [Col1]
from @pointy p1
inner join @pointy p2 on p1.id < p2.id
inner join @pointy p3 on p1.id < p3.id and p2.id < p3.id
inner join @pointy p4 on p1.id < p4.id and p2.id < p4.id and p3.id < p4.id
inner join @pointy p5 on p1.id < p5.id and p2.id < p5.id and p3.id < p5.id and p4.id < p5.id
where p1.point + p2.point + p3.point + p4.point + p5.point = 50
order by newid()) as CombinationList
where charindex((',' + convert(varchar,p.id) + ','),CombinationList.[Col1])>0
August 26, 2005 at 1:06 pm
The ANSI SQL club on Yahoo tested the Microsoft RAND function and found it to be useless in the late 1990s and in .NET Microsoft tells you not to use the RAND function in any security related code. The point is if Microsoft have accepted the function to be not random I don't think you can get random result using it. Just my opinion and I could be wrong. Hope this helps.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply