I've got a huge table:
+------+---------+-----+-----+-------+------+---------+ |ticker|data_date|price|count|oper_id|ext_nr|oper_summ| +------+---------+-----+-----+-------+------+---------+ |SBER |2015-08..|70.00|15 |0 |251528|1050.00 | |AFLT |2015-08..|30.00|5 |0 |251525|150.00 | |SBER |2015-08..|69.00|10 |1 |251521|690.00 | |SBER |2015-08..|71.00|15 |1 |251513|1065.00 | |SBER |2015-08..|72.00|15 |0 |251512|1080.00 | data_date format: 2015-01-05 09:59:59 UNIQUE KEY `idx_ticker_ext_nr` (`ticker`,`ext_nr`)
I need to SELECT LAST N rows WHERE SUM(oper_summ) will be LESS THEN 10000
I've found similar topic: limiting the rows to where the sum a column equals a certain value in MySQL
SELECT
O.ext_nr,
O.price, O.count,
O.oper_summ,
(SELECT
sum(oper_summ) FROM Table1
WHERE ext_nr <= O.ext_nr) 'RunningTotal'
FROM Table1 O
HAVING RunningTotal <= 10000
but unable to make it work in my coditions...
Found a solution:
SET @msum := 0;
select t1.* from
(select m.*,
(@msum := @msum + m.oper_summ) as cumul_oper_summ from jos_fintools_data m order by m.data_date DESC )
t1 where t1.cumul_oper_summ <= 10000;
credits goes to toomanyredirects: limiting the rows to where the sum a column equals a certain value in MySQL