Najmul Hosain Najmul Hosain - 6 months ago 12
MySQL Question

MySQL select upto first occurrence of condition matching

Id | Price
----------------
1 | 10
2 | 20
3 | 40
4 | 10


I need to select ids where first occurrence of summation of price is greater than or equal 55 matching from the bottom. At this case --
I will have 4,3,2 ids selected.

Answer

Well, this is kinda tricky for MySQL since it doesn't support any window fuctions and becuase you want to include the first occurrence as well. You can try this:

SELECT * FROM (
    SELECT t.id,
           (SELECT sum(s.price) FROM YourTable s
            WHERE s.id <= t.id) as cuml_sum
    FROM YourTable t) ss
WHERE ss.cuml_sum < 55
--Will select all the record will the sum < 55
UNION ALL
SELECT * FROM (
    SELECT t.id,
           (SELECT sum(s.price) FROM YourTable s
            WHERE s.id <= t.id) as cuml_sum
    FROM YourTable t) tt
WHERE tt.cuml_sum >= 55
ORDER BY tt.cuml_sum 
LIMIT 1
--Will select the first record that have sum >= 55