By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tablename (
`client` VARCHAR(6),
`status` INTEGER,
`datetime` DATETIME
);
INSERT INTO tablename
(`client`, `status`, `datetime`)
VALUES
('Anton', '2', '2018-09-24'),
('Anton', '1', '2018-08-12'),
('Anton', '0', '2019-04-24'),
('Anton', '0', '2019-04-25'),
('Sergey', '0', '2019-09-24'),
('Sergey', '0', '2019-08-12'),
('Sergey', '0', '2019-04-24'),
('Sergey', '0', '2019-04-25');
Records: 8 Duplicates: 0 Warnings: 0
select client,
coalesce(
max(case when status > 0 then datetime end),
max(case when status = 0 then datetime end)
) datetime
from tablename
where status is not null
group by client
| client | datetime |
|---|---|
| Anton | 2018-09-24 00:00:00 |
| Sergey | 2019-09-24 00:00:00 |
select t.*
from tablename t inner join (
select client,
coalesce(
max(case when status > 0 then datetime end),
max(case when status = 0 then datetime end)
) datetime
from tablename
where status is not null
group by client
) tt on tt.client = t.client and tt.datetime = t.datetime
| client | status | datetime |
|---|---|---|
| Anton | 2 | 2018-09-24 00:00:00 |
| Sergey | 0 | 2019-09-24 00:00:00 |
select t.client, t.status, t.datetime
from (
select *,
row_number() over (
partition by client
order by case status when 0 then 1 else 0 end, datetime desc
) rn
from tablename
where status is not null
) t
where t.rn = 1
| client | status | datetime |
|---|---|---|
| Anton | 2 | 2018-09-24 00:00:00 |
| Sergey | 0 | 2019-09-24 00:00:00 |