Search code examples
sqlpostgresqlwindow-functions

Is there a way to reference the tuple below in a calculation?

i have this view here:

   x  |  y   |  z 

 -----+------+-----
    a | 645  | 
    b | 46   | 
    c | 356  | 
    d | 509  |

Is there a way to write a query for a z item to reference a different row?

For example, if i want z to be the value of the tuple below's y value - 1

So:

z.a = y.b - 1 = 46 - 1 = 45

z.b = y.c - 1 = 356 - 1 = 355

z.c = y.d - 1 = 509 - 1 = 508

Solution

  • You are describing window function lead(), which lets you access any column on the the "next" row (given a partiton and an order by criteria):

    select
    x, 
    y,
    lead(y) over(order by x) - 1 as z
from mytbale