TLDR: I need a way to limit my select such that only rows needed to fill my new order are returned.
Hey, I'm looking for a better way to do a query on the simplified toy table below. Right now I do a straightforward select then do some additional logic in Python that processes rows that are fully or partially fillable.
id |name | category | price | quantity ---+-------+----------+---------+--------- 1 |Robby | buy | 45 | 4 2 |Blane | buy | 48 | 5 3 |Shawn | buy | 43 | 10 4 |Valva | sell | 62 | 9 5 |Chris | buy | 42 | 15
If I'm inserting a new sell order at price $40 and quantity = 12
SELECT id, name, price, category FROM my_table WHERE price > 40 AND category = 'buy' ORDER BY price DESC;
Then in Python my logic looks something like this:
running_total = 0 for order in orders: running_total += order.quantity fill_order(order, new_sell_order) if running_total >= new_sell_order.quantity: # Values is 12 in my example. break
I'd like a way to limit my select such that all rows are returned in order until a row is returned such that the sum of all quantity columns is greater than some value. Additional columns need not be returned (and thus locked in a repeatable read state).