Assuming you have an ordering column -- say
id
-- then you can do the following in SQL Server 2012:
select col,
col - coalesce(lag(col) over (order by id), 0) as diff
from t;
In earlier versions of SQL Server, you can do almost the same thing using a correlated subquery:
select col,
col - isnull((select top 1 col
from t t2
where t2.id < t.id
order by id desc
), 0)
from t
This uses
isnull()
instead of
coalesce()
because of a "bug" in SQL Server that evaluates the first argument twice when using
coalesce()
.
You can also do this with
row_number()
:
with cte as (
select col, row_number() over (order by id) as seqnum
from t
)
select t.col, t.col - coalesce(tprev.col, 0) as diff
from t left outer join
t tprev
on t.seqnum = tprev.seqnum + 1;
All of these assume that you have some column for specifying the ordering. It might be an
id
,
or a creation date or something else. SQL tables are inherently
unordered, so there is no such thing as a "previous row" without a
column specifying the ordering
No comments :
Post a Comment