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.

Possible duplicate of [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — Clockwork-Muse, Dec 13 '18 at 07:50

score 1 · Answer 1 · answered Aug 31 '18 at 20:49

1

You need to use a LEFT OUTER JOIN between the table person and the table address, adding that the address must match the last eff_date.

You didn't say what's the database but considering you tagged it RPG, I assumed DB2:

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 address a on p.id = a.person_id
    and (a.person_id, a.eff_date) in (
      select person_id, max_date from d
    )

answered Aug 31 '18 at 20:49

The Impaler

45,731
9
39
76

Yes sorry it is DB2 good guess, there isnt any tag for "db2". But this query fails "Comparison operator IN not valid", i get the idea and i will try to implement on my end. – Wale A. Aug 31 '18 at 21:03
@WaleA. . . . Of course there is a tag for db2. Did you look? – Gordon Linoff Aug 31 '18 at 21:56

score 1 · Accepted Answer · answered Sep 04 '18 at 17:25

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

score 0 · Answer 3 · answered Aug 31 '18 at 21:36

IRL I would make a view of recentaddress.

create view recentaddress as 
    select a.* from address a 
      join ( 
       select address.id, max(eff_date) md from address
        group by address.id 
        ) as b 
        on a.id = b.id and a.eff_date= b.md

Then you can simply query person and recentaddress

select * from person a left join recentaddress b on a.person_id = b.id

or you can build an all in one.

select * from person a join (

        select a.* from address a 
          join ( 
           select address.id, max(eff_date) md from address
            group by address.id 
            ) as b 
            on a.id = b.id and a.eff_date= b.md    
) as address on a.person_id = address.id

SQL selection based on a particular field value

3 Answers3