Edwin Jarvis - 1 year ago 55

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 Source

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.