SQL Server – Get Max Serial Number by Sum of Corresponding Amount

Your question is not clear. You are trying to update the value of some table based on a value of another? This can be solved using something like the query below:

In SQL Server 2005 or newer:

with my_data_cte (
  select row_number() over (order by timestamp desc) as sequence,
  pk_vv
from (
  select timestamp, pk_v from tb1
  union all
  select timestamp, pk_v from tb2
 )
)
update my_table
set value = pk_v
from my_table mt
  inner join my_data_cte ct on mt.pk_v = mt.pk_v
where sequence = 1

Just Join the table to itself and make the appropriate sum.

This query will match each row in the table to all rows in the table with a smaller ID and the same category. You can then easily sum these rows and filter out all rows with a total greater than your limit.

Using sub queries in the where clause like you do might result in many unnecessary query executions if your sub-queries are not optimized to an INNER JOIN by the database engine:

DECLARE @Limit FLOAT
SELECT @Limit = 30

SELECT
    o1.Id
    , o1.Cost
    , o1.Category
FROM
    MyOrders o1
    -- Join all orders with same category and inferior ID
    INNER JOIN MyOrders o2 ON o2.ID <= o1.ID AND o1.Category = o2.Category
GROUP BY
    o1.Id
    , o1.Cost
    , o1.Category
HAVING
    SUM(o2.Cost) < @Limit
ORDER BY
    ID