By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table orders (
custid integer not null,
jobid integer not null,
orderNo integer not null,
orderDate date not null
);
insert into orders (custid, jobid, orderNo, orderDate) values
( 1 , 10 , 25 , '2021-12-22'), -- too old
( 1 , 10 , 26 , '2022-02-01'), -- there's a newer one
( 1 , 10 , 26 , '2022-02-09'),
( 3 , 5 , 28 , '2022-01-10'),
( 3 , 6 , 29 , '2022-01-11'),
( 4 , 7 , 30 , '2021-12-31'); -- too old
select custid, jobid, orderNo, orderDate
from (
select
*,
row_number() over ( partition by jobid order by orderDate desc ) as newest
from orders
where orderDate >= '2022-01-01'
) a
where a.newest = 1;
| custid | jobid | orderNo | orderDate |
|---|---|---|---|
| 3 | 5 | 28 | 2022-01-10 |
| 3 | 6 | 29 | 2022-01-11 |
| 1 | 10 | 26 | 2022-02-09 |