MySQL 8.0

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