Interesting problem ... Assuming that it's not random (i.e. sorted by ID), you can do it using this query:
Code:
SELECT
t1.id, SUM(t2.amount) AS total
FROM
the_table t1
LEFT JOIN the_table t2 ON t2.key_id <= t1.key_id
GROUP BY
1
HAVING
total < 24
ORDER BY
t1.id
The thing is ... you'll have to be able to find the sum of amount from the previous rows. I achieve this by self joining the table on id is less than or equal to existing row.
When you start randomizing the rows, you somehow need to know which rows to self join so that you get the right sum. Obviously by using id won't do it. What you need is to somehow get this result:
Code:
3, 10, 3, 10
1, 10, 3, 10
1, 10, 1, 10
2, 25, 3, 10
2, 25, 1, 10
2, 25, 2, 25
4, 5, 3, 10
4, 5, 1, 10
4, 5, 2, 25
4, 5, 4, 5
Hence when you group it by the first ID, you'll get the following result:
Code:
3, 10, 10
1, 10, 20
2, 25, 45
4, 5, 50
It's doable, I think, but because of the nature of random, you can't self join a random table with another random table as if it's truly random the tables would be different. You can select the rows randomly from the table and put it to temporary table and add row number in sequence and modify my query and run it on the temp table instead.
In short, you create a temp table as the result of random select similar to this (the first column is row_number; the second is your id, the third is value)
Code:
1, 3, 10
2, 1, 10
3, 2, 25
4, 4, 5
dBforums
>
Finding a position based on SUM...
10-30-09, 16:38
Linear Mode
