Edwin Jarvis - 8 months ago 27

SQL Question

I need to calculate the difference of a column between two lines of a table, there is 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 that with a select like this 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?

Answer

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.