SQL Server CE: calculate same column values (problems are null's after deleting rows)

Question

I want to calculate same column values in SQL Server CE. Problem are null's after deleting rows because id's are not in order: 1,2,3,4,5... they are 1,2,3,5,6...

Screenshot: https://i.sstatic.net/xKxt8.jpg

Query

SELECT  
    t2.id, t2.datum,
    t2.stanjekm,
    t2.tocenolit,
    t2.stanjekm - t1.stanjekm as PredjenoKm
FROM
    Gorivo AS t1 
RIGHT JOIN
    Gorivo AS t2 ON t1.id = t2.id-1

How to insert this id:

SELECT MAX(id) 
FROM gorivo 
WHERE id NOT IN (SELECT MAX(id) FROM gorivo)

instead of:

t2.id-1

Answer 1

You are actually looking for the LAG function which looks at a value of the previous row.
And as you can see, no need for self-join.

LAG (Transact-SQL)

SELECT  id, 
        datum,
        stanjekm,
        tocenolit,
        stanjekm - lag (stanjekm,1,0) over (order by id) as PredjenoKm
FROM Gorivo as 

OR

;with    t as
        (
                SELECT  id, 
                        datum,
                        stanjekm,
                        tocenolit,
                        stanjekm,
                        row_number () over (order by id) as rn

                FROM Gorivo as 
        )

select  t1.id, 
        t1.datum,
        t1.stanjekm,
        t1.tocenolit,
        t1.stanjekm - isnull(t2.stanjekm,0)
from    t as t1 left join t as t2 on t2.rn = t1.rn - 1