Windowing ? - Mailing list pgsql-sql

Hi,

I have a table describing sort of ordered road segments and their associated weight. I would like to calculate weight deltas between two consecutive segments. Problem is to substract previous from current row and reuses result as input for the next row. I tried some windowing tests but was unsuccessful.

Input table :

road   segment       weight

1        1                 80

1        2                 5

1        3                 3

2        1                 75

2        2                 8

2        3                 12

2        4                 3

The result should be a new delta column :

road   segment       weight delta

1        1                 80                80      (80-0) first row

1        2                 5                 75      (80-5)

1        3                 3                 72      (75-3)

2        1                 75                75      (75-0)

2        2                 8                 67      (75-8)

2        3                 12                55      (67-12)

2        4                 3                 52      (55-3)

Thanks for any help,

By the way, thanks again to Andreas and Martin that unstucked my mind yesterday as I was searching for complicated solutions when a very simple one existed.

Have a good day (or good evening)

Olivier