How to get the first row which exceeds a specific amount in SQL

user_213689 0
Viewed 47

I have a table which contains the following data:

ID Amount Date
1 500 2021-05-01
2 100 2021-05-03
3 300 2021-05-06

I need to get the first record which exceeds a specific amount, such as the following examples:

  • If specific amount is 500, we return ID 1
  • If specific amount is 550, we return ID 2
  • If specific amount if 600, we return ID 2
  • If specific amount > 600, we return ID 3

How can this be achieved using a MySQL query?

mariadb mysql sql
1 Answers

You can use a cumulative sum and some filtering:

select t.*
from (select t.*,
             sum(amount) over (order by date) as running_amount
      from t
     ) t
where running_amount >= @threshold and
      (running_amount - amount) < @threshold;
user_11440350
Related