SQL selection based on a particular field value

Question

I have a table of people and a table of their addresses. A person can have multiple addresses, but there is an effective date attached to each address.

I want to link people and addresses to their greatest effective date and I am stuck with my query attached below. I am getting just the maximum effective date in the entire table. Please note that this is RPG so the dates are stored like numbers, for instance today would be 20180831 in YYYYMMDD format (2018-08-31).

 SELECT 
   PERSON.ID, PERSON.NAME, ADDRESS.ID, ADDRESS.ADD1
 , ADDRESS.ADD2, ADDRESS.CITY 
   FROM PERSON
   LEFT JOIN 
     (
      SELECT * 
        FROM ADDRESS
          WHERE EFF_DATE IN (SELECT MAX(EFF_DATE) FROM ADDRESS)
     ) AS A
   ON PERSON.ID = A.ID

I know the problem is in the WHERE clause but I'm drawing a blank.

Answer 1

The Impaler has a nice solution, but I would improve it by removing the sub-query like this:

with d as (
  select person_id, max(eff_date) as max_date 
    from address 
    group by person_id
)
select p.*
  from person p
  left join d on p.id = d.person_id
  left join address a 
    on p.id = a.person_id and a.eff_date = d.max_date