I want to add a column to a SQL query containing an immediately preceding (by date) value from another column

I have a table of customer records, where each customer can have multiple records, if their old record has been closed (they can only have one "Live" record).

Each record has a created date and a closed date (if it has been closed).

Source table:

I want a SQL statement which will return the table, with a new column old_record_id.

This will be populated with the record_id for the previous record for that customer. "Previous record" is the record with the closest previous created_date to the created_date of that record. If there are no previous records, it should be null.

Results I want from that SQL query:

LAG would work for this (as mentioned in the comments). Here is a snippet:

SELECT customer_id,
       record_id,
       record_status,
       created_date,
       closed_date,
       LAG (record_id) OVER (PARTITION BY customer_id ORDER BY created_date) as old_record_id
FROM CUSTOMER_RECORDS

Just a note: this will produce slightly different results than what you've got because for ID = 1000003, the created date (07/03/2025 13:04:33) for the old ID is well before the closed date (03/12/2024 22:19:39).