Вы можете использовать объединение с подзапросом, сгруппированным по domestic_helper_id
с агрегированной функцией, например: max()
SELECT d.*
FROM domestic_helper_idcard d
inner join (
select domestic_helper_id, max(id) max_id
from domestic_helper_idcard
where is_deleted = 0
group by domestic_helper_id
) t on t.domestic_helper_id = d.domestic_helper_id and t.max_id = d.id
order by d.card_expiration_date desc
и в соответствии с предложением Йенса после уточнения с использованием max card_expiration_date
SELECT d.*
FROM domestic_helper_idcard d
inner join (
select domestic_helper_id, max(card_expiration_date) max_date
from domestic_helper_idcard
where is_deleted = 0
group by domestic_helper_id
) t on t.domestic_helper_id = d.domestic_helper_id and t.max_date = d.max_date
order by d.card_expiration_date desc