limiting the rows to where the sum a column equals a certain value [duplicate]

Question

i m trying to limit the rows to where the sum a column equals a certain value but i m getting unexpected results.

CREATE TABLE buy (
  id int not null,
  volume decimal(9,4) not null,
  price decimal(9,2) not null
 );

insert buy (id, volume, price) values 
(1, 1.0000, 14250.00),
(2, 0.2500, 14251.10),
(3, 0.2500, 14250.00),
(4, 0.0050, 14200.00),
(5, 0.5000, 14255.01);

expected result selected ids 5,2,1(because ordering by higest price) but it selects ids 2,1,3

SELECT t.id, t.volume, t.price,
(SELECT SUM(volume) FROM buy WHERE id <= t.id) 'RequiredVolume'
FROM buy t
HAVING RequiredVolume <= '1.5000'
ORDER BY price DESC, id ASC;

| id | volume |   price | RequiredVolume |
|----|--------|---------|----------------|
|  2 |   0.25 | 14251.1 |           1.25 |
|  1 |      1 |   14250 |              1 |
|  3 |   0.25 |   14250 |            1.5 |

http://sqlfiddle.com/#!9/ef024/3/0

Answer 1

I think the problem could be due to the nested query being >= and not =.

This gives the output you described:

SELECT t.id, t.volume, t.price,
(SELECT SUM(volume) FROM buy WHERE id = t.id) 'RequiredVolume'
FROM buy t
HAVING RequiredVolume <= '1.5000'
ORDER BY price DESC, id ASC;

Memo - Can you please post what your expected results would be for all fields and not just the ID ordering.

Answer 2

I'm not a MySQL guy, but maybe this works for you though it will only return records 5 and 2 because records 5, 2, and 1 have a rolling sum of 1.75 instead of the 1.5 you're filtering for.

SELECT t.id, t.volume, t.price,
(SELECT SUM(volume) FROM buy WHERE (id <> t.id AND price > t.price) OR (id < t.id AND price = t.price) OR (id = t.id)) 'RequiredVolume'
FROM buy t
HAVING RequiredVolume <= 1.5
ORDER BY price DESC, id ASC;

Here's the SQLFiddle with the HAVING clause commented out to show this would only return records 5 and 2 in the event this logic is indeed what you're after.