How to make LEFT JOIN with row having max date?

Question

I have two tables in Oracle DB

Person (
  id
)

Bill (
  id,
  date,
  amount,
  person_id
)

I need to get person and amount from last bill if exist. I trying to do it this way

SELECT
  p.id,
  b.amount
FROM Person p
LEFT JOIN Bill b
ON b.person_id = p.id AND b.date = (SELECT MAX(date) FROM Bill WHERE person_id = 1)
WHERE p.id = 1;

But this query works only with INNER JOIN. In case of LEFT JOIN it throws ORA-01799 a column may not be outer-joined to a subquery

How can I get amoun from the last bill using left join?

Answer 1

Please try the below avoiding sub query to be outer joined

SELECT
 p.id,
 b.amount
 FROM Person p
 LEFT JOIN(select * from Bill where date =
 (SELECT MAX(date) FROM Bill b1 WHERE person_id = 1)) b ON b.person_id = p.id 
 WHERE p.id = 1;

Answer 2

What you are looking for is a way to tell in bills, for each person, what is the latest record, and that one is the one to join with. One way is to use row_number:

select * from person p
left join (select b.*, 
                  row_number() over (partition by person_id order by date desc) as seq_num 
           from bills b) b
on p.id = b.person_id
and seq_num = 1