sql server - Order by in SQL Cursor

Question

I am trying to insert cumulative value (CV) into a column using cursor. I would like to calculate cumulative value (CV) order by a date (DT) column.

Example:

CREATE TABLE #unsorted (ID INT, DT DATE, CV INT)
INSERT INTO #unsorted VALUES(1, '2019-01-01', 0)
INSERT INTO #unsorted VALUES(2, '2018-12-15', 0)
INSERT INTO #unsorted VALUES(3, '2017-01-01', 0)

DECLARE @CurrentID INT
DECLARE @CV INT = 0
DECLARE c CURSOR FORWARD_ONLY FOR
SELECT ID FROM #unsorted FOR UPDATE OF CV

    OPEN c

    FETCH NEXT FROM c INTO @CurrentID

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @CV = @CV + @CurrentID
        UPDATE #unsorted
        SET CV = @CV
        WHERE CURRENT OF c

        FETCH NEXT FROM C INTO @CurrentID
    END

    CLOSE c
    DEALLOCATE c

SQL server does not allow to change the SELECT ID FROM #unsorted FOR UPDATE OF CV statement to SELECT ID FROM #unsorted ORDER BY dt ASC FOR UPDATE OF CV.

It throws The cursor is READ ONLY. error.

Expected result:

ID  DT          CV
3   2017-01-01  3
2   2018-12-15  5
1   2019-01-01  6

Current result:

ID  DT          CV
1   2019-01-01  1
2   2018-12-15  3
3   2017-01-01  6

Please check the value of column CV.

How can I sort data before calculating cumulative value (CV)?

EDIT: One of the solutions is to declare DT column as primary key so it will keep the data sorted in the table. However, I think it is not the best option.

Answer 1

You can use SUM() with OVER() to generate cumulative totals. In this particular case :

select ID, DT, sum(id) over(order by dt) AS CV
from unsorted 
order by dt

Produces :

3   2017-01-01  3
2   2018-12-15  5
1   2019-01-01  6

OVER() and analytic functions like LAG and LEAD can solve many problems that required cursors or complex solutions in the past. For example, LAG() OVER... can be used to find the difference between the current row in an ordered result set and the previous one, or detect value changes, making it easier to find ranges or gaps in a sequence