[SQL] 'Jagged' self join?

[SiD the Turtle]

Hi guys, I have a bit of a puzzle I'm getting nowhere with. Consider the following table:

id        | value      | auditkey
---------------------------------
1         | 10         | 5
1         | 8          | 3
1         | 4          | 2
2         | 20         | 5
2         | 4          | 4

What I need to do is turn this data into the form that for each id, I have the difference in value between one row and the next smallest audit key, see below with additional explanation:

id        | diff
----------------
1         | 2         (Auditkey 5->3: 10 - 8 = 2
1         | 4         (Auditkey 3->2: 8 - 4 = 4
2         | 16        (Auditkey 5->4: 20 - 4 = 16

So in theory what I want to do is select the entire table, order it by id and then audit key (descending) and then join it to the same table shifted down one (ish, the last row for each id would end up being ignored).

Any ideas? This is in SQL Server 2005 if it helps, so I don't have access to unique functions like MySQL's LIMIT.

 

[SiD the Turtle]

n

Basically every day more data is loaded, therefore there will be a new auditkey (an autoincrement) every day.

 

[SiD the Turtle]

Thanks matey. My implementation of your idea (the actual data is a bit more complex) was a bit expensive but we're still only talking seconds here as I don't have reams of data.

In the end I created quite a nasty intermediate table that maps the id/auditkey pair to the id/auditkey I want to find the difference of, and then joined this to the actual table. Messy, only slightly faster than a cursor (lots of joins going on) but will be easier to read when I come to look at the code in a few months time