Access the "previous row" value in a SELECT statement

Question

I need to calculate the difference of a column between two lines of a table. Is there any way I can do this directly in SQL? I'm using Microsoft SQL Server 2008.

I'm looking for something like this:

SELECT value - (previous.value) FROM table

Imagining that the "previous" variable reference the latest selected row. Of course with a select like that I will end up with n-1 rows selected in a table with n rows, that's not a probably, actually is exactly what I need.

Is that possible in some way?

Euryale

Well just adding for a comment useful for new viewers further. SQL 2012 has LAG and LEAD now :) Refer this link blog.sqlauthority.com/2013/09/22/… – Nameless

Answer 1

SQL has no built in notion of order, so you need to order by some column for this to be meaningful. Something like this:

select t1.value - t2.value from table t1, table t2 
where t1.primaryKey = t2.primaryKey - 1

If you know how to order things but not how to get the previous value given the current one (EG, you want to order alphabetically) then I don't know of a way to do that in standard SQL, but most SQL implementations will have extensions to do it.

Here is a way for SQL server that works if you can order rows such that each one is distinct:

select  rank() OVER (ORDER BY id) as 'Rank', value into temp1 from t

select t1.value - t2.value from temp1 t1, temp1 t2 
where t1.Rank = t2.Rank - 1

drop table temp1

If you need to break ties, you can add as many columns as necessary to the ORDER BY.

Partook

That's fine, order is not a issue, I just removed it from the example to make it simpler, I gonna try that. – Euryale

which assumes, that primary keys are generated sequentially and rows are never deleted and the select doesn't have any other order clause and and and ... – Ezaria

Martin is correct. Although this might work in some cases you really need to define exactly what you mean by "previous" in a business sense, preferably without relying on a generated ID. – Gaillard

You're right, I added an improvement using a SQL Server extension. – Partook

That won't run often in the database, and the rows are never deleted. I believe it gonna work fine. – Euryale

Remember you might have gaps due to rollbacks. – Utrecht

In response to "That's fine, order is not an issue"... Then why don't you just subtract an arbitraty value in your query since that is what you are doing if you don't consider order? – Persia

Answer 2

Use the lag function:

SELECT value - lag(value) OVER (ORDER BY Id) FROM table

Sequences used for Ids can skip values, so Id-1 does not always work.

Catholicism

This is PostgreSQL solution. The question is about MSSQL. MSSQL has such function in versions 2012+ (msdn.microsoft.com/en-us/en-en/library/hh231256(v=sql.120).aspx) – Tumbler

@KromStern Not only PostgreSQL solution. SQL Window functions were introduced in SQL:2003 standard. – Catholicism

The LAG function can take three parameters: LAG(ExpressionToSelect, NumberOfRowsToLag, DefaultValue). The default number of rows to lag is 1, but you can specify that and the default value to select when it's not possible to lag since you're at the beginning of the set. – Ophiolatry

Answer 3

Oracle, PostgreSQL, SQL Server and many more RDBMS engines have analytic functions called LAG and LEAD that do this very thing.

In SQL Server prior to 2012 you'd need to do the following:

SELECT  value - (
        SELECT  TOP 1 value
        FROM    mytable m2
        WHERE   m2.col1 < m1.col1 OR (m2.col1 = m1.col1 AND m2.pk < m1.pk)
        ORDER BY 
                col1, pk
        )
FROM mytable m1
ORDER BY
      col1, pk

, where COL1 is the column you are ordering by.

Having an index on (COL1, PK) will greatly improve this query.

Phyletic

SQL Server 2012 now has LAG and LEAD as well. – Carvel

Hana SQL script also supports LAG and LEAD. – Pierian

Just to add another comment to viewers that arrived here looking for doing that in Hive. It also has LAG and LEAD functions. Documentation here: cwiki.apache.org/confluence/display/Hive/… – Numerous

Answer 4

WITH CTE AS (
  SELECT
    rownum = ROW_NUMBER() OVER (ORDER BY columns_to_order_by),
    value
  FROM table
)
SELECT
  curr.value - prev.value
FROM CTE cur
LEFT JOIN CTE prev on prev.rownum = cur.rownum - 1

Shoestring

It works correctly if there is no grouping in the query, but what if we want to subtract values from previous value only within a group, lets say same EmployeeID, then how can we do that? Coz running this works only for the top 2 rows of each group and not to the rest of the rows in that group. For this, I used running this code in while loop, but that seems to be very slow. Any other approach we could in this scenario? And that too only in SQL Server 2008? – Pupa

Answer 5

LEFT JOIN the table to itself, with the join condition worked out so the row matched in the joined version of the table is one row previous, for your particular definition of "previous".

Update: At first I was thinking you would want to keep all rows, with NULLs for the condition where there was no previous row. Reading it again you just want that rows culled, so you should an inner join rather than a left join.

---

Update:

Newer versions of Sql Server also have the LAG and LEAD Windowing functions that can be used for this, too.

Tosh

Answer 6

select t2.col from (
select col,MAX(ID) id from 
(
select ROW_NUMBER() over(PARTITION by col order by col) id ,col from testtab t1) as t1
group by col) as t2

Lorgnon

Answer 7

The selected answer will only work if there are no gaps in the sequence. However if you are using an autogenerated id, there are likely to be gaps in the sequence due to inserts that were rolled back.

This method should work if you have gaps

declare @temp (value int, primaryKey int, tempid int identity)
insert value, primarykey from mytable order by  primarykey

select t1.value - t2.value from @temp  t1
join @temp  t2 
on t1.tempid = t2.tempid - 1

Utrecht

Answer 8

Another way to refer to the previous row in an SQL query is to use a recursive common table expression (CTE):

CREATE TABLE t (counter INTEGER);

INSERT INTO t VALUES (1),(2),(3),(4),(5);

WITH cte(counter, previous, difference) AS (
  -- Anchor query
  SELECT MIN(counter), 0, MIN(counter)
  FROM t
  UNION ALL
  -- Recursive query
  SELECT t.counter, cte.counter, t.counter - cte.counter
  FROM t JOIN cte ON cte.counter = t.counter - 1
)
SELECT counter, previous, difference
FROM cte
ORDER BY counter;

Result:

counter	previous	difference
1	0	1
2	1	1
3	2	1
4	3	1
5	4	1

The anchor query generates the first row of the common table expression cte where it sets cte.counter to column t.counter in the first row of table t, cte.previous to 0, and cte.difference to the first row of t.counter.

The recursive query joins each row of common table expression cte to the previous row of table t. In the recursive query, cte.counter refers to t.counter in each row of table t, cte.previous refers to cte.counter in the previous row of cte, and t.counter - cte.counter refers to the difference between these two columns.

Note that a recursive CTE is more flexible than the LAG and LEAD functions because a row can refer to any arbitrary result of a previous row. (A recursive function or process is one where the input of the process is the output of the previous iteration of that process, except the first input which is a constant.)

I tested this query at SQLite Online.

Oscine

Answer 9

LAG() and LEAD() are positional functions. LAG() refers to data from previous row and LEAD() refers to data after current row.

Here is the code:

SELECT value - LAG(value) OVER(ORDER BY id) FROM table;

Guib

Answer 10

You can use the following funtion to get current row value and previous row value:

SELECT value,
min(value) over (order by id rows between 1 preceding and 1 
preceding) as value_prev
FROM table

Then you can just select value - value_prev from that select and get your answer

Bullshit

How is that better than lag(value) over (order by id) – Actomyosin

I don't know. But it works too – Bullshit