# Is there a way to access the “previous row” value in a SELECT statement?

###### Posted By: Augusto Radtke

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?

## Solution

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.

###### Answered By: Anonymous

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.