Joining tables on 2 conditions caused the creation of a duplicate column. If I try to SELECT that column I get an ''Invalid Identifier'' error

Question

Table: A

| ID  | Name    | 
| --  | ------- |
| 1   | John    |
| 2   | Micheal |
| 3   | Mary    |

Table B:

| ID  | Date     | Bonus |
| --  | -------- | ----- |
| 1   | 01/01/09 | 1200  |
| 1   | 01/07/12 | 4000  |
| 1   | 12/05/14 | 2500  |
| 3   | 01/01/09 | 0     |
| 3   | 11/10/17 | 1850  |
| 2   | 08/01/16 | 2500  |
| ... |    ...   |  ...  |

I have these 2 tables. My goal to write a query that retreives the name, the lastest date available for that person and the bonus coresponding to both that date and that name.

Here's the code I have,

SELECT NAME, date_max, BONUS FROM 
((SELECT A.ID, MAX(A.DATE) as date_max FROM 
table_A A JOIN table_B B 
USING (ID) 
GROUP BY ID) C 
JOIN table_B D 
ON (C.ID = D.ID AND date_max = D.DATE))

The problem is that by joining on two conditions (last line) I duplicated the ID column in the result of my inner query and now when I try to execute the outer SELECT I get the invalid identifier error. Please help me fix this

Answer 1

Using pure sql, a way would be this:

select ta.name, tb.Date, tb.Bonus
from TableA ta
  left join TableB tb
      on ta.ID = tb.ID
  left join (
    select ID, max(b.Date) as Date
    from TableA a
      join TableB b
        on a.ID = b.ID
      group by ID
    ) m
  on ta.ID = m.ID
 and tb.date = m.date   

Or most voted answer in: Fetch the row which has the Max value for a column

Using window functions, another way would be:

 select t.name, t.Date, t.Bonus,
 from (
   select ta.name, tb.Date, tb.Bonus,
     max(tb.Date) over (partition by ta.ID) as maxDate
   from TableA ta
     left join TableB tb
       on ta.ID = tb.ID
     ) t
 where t.maxDate = t.Date