How to make LEFT JOIN with row having max date?

user_3530799 0
Viewed 19379

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?

greatest-n-per-group left-join group-by oracle sql
10 Answers

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;
user_12977920

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
user_64005260

You cannot have a subquery inside an ON statement. Instead you need to convert your LEFT JOIN statement into a whole subquery.

Not tested but this should work.

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

I'm not quite sure why you would want to filter for the date though. Simply filtering for the person_id should do the trick

user_26747950

Hey you can do like this 

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
user_50512550

you should join Person and Bill to the result for max date in bill related to person_id 

  select Person.id, bill.amount
  from Person
  left join bill on  bill.person_id = person.id
  left join (
  select person_id, max(date) as max_date
  from bill 
  group by person_id ) t on t.person_id = Person.id and b.date = t.max_date
user_35223120 
SELECT
  p.id,
  b.amount
FROM Person p
LEFT JOIN Bill b
ON b.person_id = p.id 
WHERE (SELECT max(date) FROM bill AS sb WHERE sb.person_id=p.id LIMIT 1)=b.date;
user_86943770 
 SELECT
      p.id,
      c.amount
    FROM Person p
    LEFT JOIN (select b.person_id as personid,b.amount as amount from Bill b where b.date1= (select max(date1) from Bill where person_id=1)) c
    ON c.personid = p.id
    WHERE p.id = 1;
user_50142210

try this

select * from person p
left join (select  MAX(id) KEEP (DENSE_RANK FIRST ORDER BY date DESC) 
           from bills b) b
on p.id = b.person_id
user_13328700

I use GREATEST() function in join condition:

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

This allows you to grab the whole row if necessary and grab the top x rows

SELECT p.id
  ,b.amount
FROM person p
    LEFT JOIN 
    (
        SELECT * FROM 
        (
            SELECT date
                ,ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY date DESC) AS row_num 
            FROM bill
        )
        WHERE row_num = 1
    ) b ON p.id = b.person_id
WHERE p.id = 1
;
user_58477950
Related