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

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

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?

CodePudding user response：

You need to PARTITION BY the ID column.

 lag(pc.CountryArrival) over (PARTITION BY ID order by DateArrival) as DeparturePort