Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table some_test_data
  (
    id integer,
    job_date date
  );
insert into some_test_data values
  (1, date '2023-06-01'),
  (1, date '2023-06-01'),
  (1, date '2023-06-01'),
  (1, date '2023-07-01'),
  (1, date '2023-07-01'),
  (1, date '2023-07-16'),
  (1, date '2023-07-16'),
  (1, date '2023-07-16');
8 rows affected

select s.*,
       max(job_date) over (
         partition by id
         order by job_date
         range between unbounded preceding and 1 preceding) as last_distinct_job_date
  from some_test_data s


ID
JOB_DATE
LAST_DISTINCT_JOB_DATE


1
01-JUN-23
null

1
01-JUN-23
null

1
01-JUN-23
null

1
01-JUL-23
01-JUN-23

1
01-JUL-23
01-JUN-23

1
16-JUL-23
01-JUL-23

1
16-JUL-23
01-JUL-23

1
16-JUL-23
01-JUL-23


select t1.*,
       ( select max(t2.job_date)
           from some_test_data t2
          where t1.id = t2.id
            and t1.job_date > t2.job_date
       ) as last_distinct_job_date
  from some_test_data t1


ID
JOB_DATE
LAST_DISTINCT_JOB_DATE


1
01-JUN-23
null

1
01-JUN-23
null

1
01-JUN-23
null

1
01-JUL-23
01-JUN-23

1
01-JUL-23
01-JUN-23

1
16-JUL-23
01-JUL-23

1
16-JUL-23
01-JUL-23

1
16-JUL-23
01-JUL-23

ID	JOB_DATE	LAST_DISTINCT_JOB_DATE
1	01-JUN-23	null
1	01-JUN-23	null
1	01-JUN-23	null
1	01-JUL-23	01-JUN-23
1	01-JUL-23	01-JUN-23
1	16-JUL-23	01-JUL-23
1	16-JUL-23	01-JUL-23
1	16-JUL-23	01-JUL-23