Edit Question - KnowledgeHub

Title Body

Imagine the following table (called TestTable ): id somedate somevalue -- -------- --------- 45 01/Jan/09 3 23 08/Jan/09 5 12 02/Feb/09 0 77 14/Feb/09 7 39 20/Feb/09 34 33 02/Mar/09 6 I would like a query that returns a running total in date order, like: id somedate somevalue runningtotal -- -------- --------- ------------ 45 01/Jan/09 3 3 23 08/Jan/09 5 8 12 02/Feb/09 0 8 77 14/Feb/09 7 15 39 20/Feb/09 34 49 33 02/Mar/09 6 55 I know there are various ways of doing this in SQL Server 2000 / 2005 / 2008. I am particularly interested in this sort of method that uses the aggregating-set-statement trick: INSERT INTO @AnotherTbl(id, somedate, somevalue, runningtotal) SELECT id, somedate, somevalue, null FROM TestTable ORDER BY somedate DECLARE @RunningTotal int SET @RunningTotal = 0 UPDATE @AnotherTbl SET @RunningTotal = runningtotal = @RunningTotal + somevalue FROM @AnotherTbl ... this is very efficient but I have heard there are issues around this because you can't necessarily guarantee that the UPDATE statement will process the rows in the correct order. Maybe we can get some definitive answers about that issue. But maybe there are other ways that people can suggest? edit: Now with a SqlFiddle with the setup and the 'update trick' example above

Tags (comma-separated) Cancel