StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Let's load your sample data from the question</p> <pre><code>mysql> drop database if exists javier; Query OK, 1 row affected (0.02 sec) mysql> create database javier; Query OK, 1 row affected (0.01 sec) mysql> use javier Database changed mysql> create table mytable -> ( -> id int not null auto_increment, -> qty1 float,qty2 float,qty3 float,qty4 float, -> primary key (id) -> ); Query OK, 0 rows affected (0.08 sec) mysql> insert into mytable (qty1,qty2,qty3,qty4) values -> ( 0.0 , 0.0 , 10 , 20 ),( 1.5 , 0.0 , 7.5 , 18 ), -> ( 1.0 , 2.0 , 7.5 , 18 ),( 0.0 , 0.5 , 5 , 13 ); Query OK, 4 rows affected (0.05 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from mytable; +----+------+------+------+------+ | id | qty1 | qty2 | qty3 | qty4 | +----+------+------+------+------+ | 1 | 0 | 0 | 10 | 20 | | 2 | 1.5 | 0 | 7.5 | 18 | | 3 | 1 | 2 | 7.5 | 18 | | 4 | 0 | 0.5 | 5 | 13 | +----+------+------+------+------+ 4 rows in set (0.00 sec) mysql> </code></pre> <h1><strong>FINAL QUERY THAT FULLY WORKS</strong></h1> <pre><code>select BBBB.* from (select id,sums FROM (select A.id,A.sums from (select id,(select sum(qty1+qty2+qty3+qty4) from mytable BB where BB.id<=AA.id) sums from mytable AA order by id) A INNER JOIN (SELECT 50 mylimit) B ON A.sums <= B.mylimit) AAA UNION (select A.id,A.sums from (select id,(select sum(qty1+qty2+qty3+qty4) from mytable BB where BB.id<=AA.id) sums from mytable AA order by id) A where A.sums=(select min(A.sums) sums from (select id, (select sum(qty1+qty2+qty3+qty4) from mytable BB where BB.id<=AA.id) sums from mytable AA order by id) A INNER JOIN (SELECT 50 mylimit) B ON A.sums >= B.mylimit))) AAAA JOIN mytable BBBB USING (id); </code></pre> <h1><strong>FINAL QUERY COMPLEMENT THAT FULLY WORKS</strong></h1> <pre><code>select BBBB.* from mytable BBBB LEFT JOIN (select id,sums FROM (select A.id,A.sums from ( select id,(select sum(qty1+qty2+qty3+qty4) from mytable BB where BB.id<=AA.id) sums from mytable AA order by id) A INNER JOIN (SELECT 50 mylimit) B ON A.sums <= B.mylimit) AAA UNION (select A.id,A.sums from (select id, (select sum(qty1+qty2+qty3+qty4) from mytable BB where BB.id<=AA.id) sums from mytable AA order by id) A where A.sums=(select min(A.sums) sums from ( select id,(select sum(qty1+qty2+qty3+qty4) from mytable BB where BB.id<=AA.id) sums from mytable AA order by id) A INNER JOIN (SELECT 50 mylimit) B ON A.sums >= B.mylimit))) AAAA USING (id) WHERE AAAA.id IS NULL; </code></pre> <p>Here is the output for 57</p> <pre><code>mysql> select BBBB.* from (select id,sums FROM (select A.id,A.sums from -> (select id,(select sum(qty1+qty2+qty3+qty4) from mytable BB -> where BB.id<=AA.id) sums from mytable AA order by id) A -> INNER JOIN (SELECT 57 mylimit) B ON A.sums <= B.mylimit) AAA -> UNION -> (select A.id,A.sums from (select id,(select sum(qty1+qty2+qty3+qty4) -> from mytable BB where BB.id<=AA.id) sums from mytable AA order by id) A -> where A.sums=(select min(A.sums) sums from (select id, -> (select sum(qty1+qty2+qty3+qty4) from mytable BB where BB.id<=AA.id) sums -> from mytable AA order by id) A INNER JOIN (SELECT 57 mylimit) B -> ON A.sums >= B.mylimit))) AAAA JOIN mytable BBBB USING (id); +----+------+------+------+------+ | id | qty1 | qty2 | qty3 | qty4 | +----+------+------+------+------+ | 1 | 0 | 0 | 10 | 20 | | 2 | 1.5 | 0 | 7.5 | 18 | +----+------+------+------+------+ 2 rows in set (0.00 sec) mysql> select BBBB.* from mytable BBBB LEFT JOIN -> (select id,sums FROM (select A.id,A.sums from ( -> select id,(select sum(qty1+qty2+qty3+qty4) -> from mytable BB where BB.id<=AA.id) sums -> from mytable AA order by id) A INNER JOIN -> (SELECT 57 mylimit) B ON A.sums <= B.mylimit) AAA -> UNION -> (select A.id,A.sums from (select id, -> (select sum(qty1+qty2+qty3+qty4) from mytable BB -> where BB.id<=AA.id) sums from mytable AA order by id) A -> where A.sums=(select min(A.sums) sums from ( -> select id,(select sum(qty1+qty2+qty3+qty4) from mytable BB -> where BB.id<=AA.id) sums from mytable AA order by id) A -> INNER JOIN (SELECT 57 mylimit) B ON A.sums >= B.mylimit))) AAAA -> USING (id) WHERE AAAA.id IS NULL; +----+------+------+------+------+ | id | qty1 | qty2 | qty3 | qty4 | +----+------+------+------+------+ | 3 | 1 | 2 | 7.5 | 18 | | 4 | 0 | 0.5 | 5 | 13 | +----+------+------+------+------+ 2 rows in set (0.00 sec) mysql> </code></pre> <p>Here is the output for 50</p> <pre><code>mysql> select BBBB.* from (select id,sums FROM (select A.id,A.sums from -> (select id,(select sum(qty1+qty2+qty3+qty4) from mytable BB -> where BB.id<=AA.id) sums from mytable AA order by id) A -> INNER JOIN (SELECT 50 mylimit) B ON A.sums <= B.mylimit) AAA -> UNION -> (select A.id,A.sums from (select id,(select sum(qty1+qty2+qty3+qty4) -> from mytable BB where BB.id<=AA.id) sums from mytable AA order by id) A -> where A.sums=(select min(A.sums) sums from (select id, -> (select sum(qty1+qty2+qty3+qty4) from mytable BB where BB.id<=AA.id) sums -> from mytable AA order by id) A INNER JOIN (SELECT 50 mylimit) B -> ON A.sums >= B.mylimit))) AAAA JOIN mytable BBBB USING (id); +----+------+------+------+------+ | id | qty1 | qty2 | qty3 | qty4 | +----+------+------+------+------+ | 1 | 0 | 0 | 10 | 20 | | 2 | 1.5 | 0 | 7.5 | 18 | +----+------+------+------+------+ 2 rows in set (0.00 sec) mysql> select BBBB.* from mytable BBBB LEFT JOIN -> (select id,sums FROM (select A.id,A.sums from ( -> select id,(select sum(qty1+qty2+qty3+qty4) -> from mytable BB where BB.id<=AA.id) sums -> from mytable AA order by id) A INNER JOIN -> (SELECT 50 mylimit) B ON A.sums <= B.mylimit) AAA -> UNION -> (select A.id,A.sums from (select id, -> (select sum(qty1+qty2+qty3+qty4) from mytable BB -> where BB.id<=AA.id) sums from mytable AA order by id) A -> where A.sums=(select min(A.sums) sums from ( -> select id,(select sum(qty1+qty2+qty3+qty4) from mytable BB -> where BB.id<=AA.id) sums from mytable AA order by id) A -> INNER JOIN (SELECT 50 mylimit) B ON A.sums >= B.mylimit))) AAAA -> USING (id) WHERE AAAA.id IS NULL; +----+------+------+------+------+ | id | qty1 | qty2 | qty3 | qty4 | +----+------+------+------+------+ | 3 | 1 | 2 | 7.5 | 18 | | 4 | 0 | 0.5 | 5 | 13 | +----+------+------+------+------+ 2 rows in set (0.01 sec) mysql> </code></pre> <p>Please remember to set the number for mylimit in the <code>(SELECT 50 mylimit)</code> subquery twice each.</p> <p>Please tell I got this one...</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload