SQL Subtract the first row from all selected rows

Question

Is there any way to subtract the value of the first selected row from all rows? Therefore if i have

t = 1, v = 500 t = 2, v = 800 t = 3, v = 1200 

I would get

 t = 1, v = 0 t = 2, v = 300 t = 3, v = 700 

I am always looking for a portable solution, but Postgres solution works the same way :-) Thank you.

Answer 1

 SELECT v - FIRST_VALUE(v) OVER (ORDER BY t) FROM mytable ORDER BY t 

Answer 2

Something like this might work

 SELECT mt2.t, mt2.v - mt1.v AS v FROM MyTable mt1 CROSS JOIN MyTable mt2 WHERE mt1.t = 1 

Answer 3

The most portable way without using window functions:

 select v - first from mytable, (select v as first from mytable order by t limit 1) as inner order by t