The Table contains data as below

Note - Running Total is calculated with respect to Part

The requirement is to fetch records where the running total should be the equal or nearest greater value with respect to stock. In the table, For Part ABC the stock value is 45. 53 is the nearest number greater than 45. For Part DEF the Stock value 15. So the Output should be

CodePudding user response：

If I understand your requirement correctly a correlated subquery is one of a few methods you could use:

select * 
from t
where running_total - stock <= (
    select Min(t2.running_total - t2.stock)
    from t t2 
    where t.part = t2.part and t2.running_total - t2.stock >= 0
);

CodePudding user response：

Using LAG function

SELECT
    part,
    quantity,
    running_total,
    stock
FROM (   
SELECT 
    *,
    LAG(running_total, 1, 0) OVER (PARTITION BY part ORDER BY running_total) AS prev_running_total
FROM parts
) t
WHERE prev_running_total < stock

Output:

fiddle