recover the value above in postgres

Question

I want to recover the value above by name.

See the table.

I would like to have a result like in the column before last number_week.

Thank you

Answer 1

Something like this should work:

SELECT number_week,
  name,
  LAG(number_week, 1, number_week) OVER (PARTITION BY name ORDER BY number_week) as before_last_number_week
FROM t1;

sqlfiddle version

This makes use of the Lag() Window Functions. A "Window" is a group of records that may optionally be ordered. Here we group up records by Name (so two groups/windows) and we order them by week_number. Then with Lag() we select the previously record's week_number (in that window). Lag() can take a default value, so we specify that the default is the current record's week_number.