0

I have a table about shipping that has information about the arrival (country and date) to a port. Now I need to extract the country where it departed from using the previous row entries. The table looks like this

ID CountryArrival DateArrival
1 BE 1-1-2022
2 US 1-1-2022
1 NL 2-1-2022
2 IT 4-1-2022
1 PT 5-1-2022

I want to obtain the departure for each ID based on the previous ArrivalDate so it would look like this

ID CountryArrival DateArrival DeparturePort
1 BE 1-1-2022 NULL
2 US 1-1-2022 NULL
1 NL 2-1-2022 BE
2 IT 4-1-2022 US
1 PT 5-1-2022 NL

I can obtain the previous Country based only on DateArrival with:

select 
 pc.*,
    lag(pc.CountryArrival) over (order by DateArrival) as DeparturePort
from shipping pc
where pc.DateArrival is not null;

Any idea how to get the previous arrival for matching IDs?

Alex Ott
  • 80,552
  • 8
  • 87
  • 132
WonderCoding
  • 3
  • 2

1 Answers1

0

You need to PARTITION BY the ID column.

 lag(pc.CountryArrival) over (PARTITION BY ID order by DateArrival) as DeparturePort
Edward Radcliffe
  • 537
  • 2
  • 11
  • Hi. This works perfectly! Quick aside as this wan't the question but is there a more efficient way of doing this without having to sort the date? – WonderCoding Apr 06 '22 at 16:31