0

I want to recover the value above by name.

See the table.

enter image description here

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

Thank you

Bak
  • 411
  • 1
  • 5
  • 19
  • I don't understand the logic for calculating this column. How did you come up with those numbers? Oh wait... I got it. Would help in the future if you explain this since it's not terribly obvious (Need the previous week's week_number, or the current week if there was no previous week for the `name`). – JNevill Jun 28 '18 at 17:17
  • I do not want to calculate. Example: for this week, I would like to get the number of the past week. number of the first week = 1 and the name = A so in the column before last number_week I put the value 1. but the second the first week = 2 and the name = A so in the column before last number_week I put the value 1. I'm sorry, I had a hard time explaining it – Bak Jun 28 '18 at 17:36

1 Answers1

0

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.

JNevill
  • 46,980
  • 4
  • 38
  • 63