SQLite Running Total Without Relying on RowId sequence

Question

So I've been looking at this for the past week and learning. I'm used to SQL Server not SQLite. I understand RowId now, and that if I have an "id" column of my own (for convenience) it will actually use RowId. I've done running totals in SQL Server using ROW_NUMBER, but that doesn't seem to be an option with SQLite. The most useful post was...

How do I calculate a running SUM on a SQLite query?

My issue is that it works as long as I have data that I will keep adding to at the "bottom" of the table. I say "bottom" and not bottom because my display of the data is always sorted based on some other column such as a month. So in other words if I insert a new record for a missing month it will get inserted with a higher "id" (aka _RowId"). My running total below that month now needs to reflect this new data for all subsequent months. This means I cannot order by "id".

With SQL Server, ROW_NUMBER took care of my sequencing because in the select where I use a.id > running.id, I would have used a.rownum > running.rownum

Here's my table

CREATE TABLE `Test` (
    `id`    INTEGER,
    `month` INTEGER,
    `year`  INTEGER,
    `value` INTEGER,
    PRIMARY KEY(`id`)
);

Here's my query

WITH RECURSIVE running (id, month, year, value, rt) AS 
(
        SELECT id, month, year, value, value
        FROM Test AS row1
        WHERE row1.id = (SELECT a.id FROM Test AS a ORDER BY a.id LIMIT 1)

        UNION ALL

        SELECT rowN.id, rowN.month, rowN.year, rowN.value, (rowN.value + running.rt)
        FROM Test AS rowN
        INNER JOIN running ON rowN.id = (
            SELECT a.id FROM Test AS a WHERE a.id > running.id ORDER BY a.id LIMIT 1
        )
)
SELECT * FROM running

I can order my CTE with year,month,id similar to how it is suggested in original example I linked above. However unless I'm mistaken that example solution relies on records in the table already ordered by year, month, id. If I'm right if I insert an earlier "month", then it will break because the "id" will have the largest value of all the _RowId_s.

Appreciate if someone can set me straight.

SQLite Running Total Without Relying on RowId sequence

0 Answers0