Joining two tables and getting the most recent records for a given name

Question

I have a system which inserts records in to two tables (Oracle db but this is a general SQL question I guess):

Person table and Person_Record table (one to one relationship)

The Person table has a date of when it was inserted, which is important later on.

What to query is: A join between the two tables but only give me the most recent data for a given person. For example:

Person Table
ID| Name |   Date
--------------------------
1 |  A   | 2012-05-01
2 |  A   | 2012-05-02
3 |  B   | 2012-05-04


Person Record Table
ID| Person_Id |   Data
--------------------------
1 |  1   | my data 1
2 |  2   | my data 2
3 |  3   | my data 3

If I do:

    SELECT pr.record_id, p.person_name,
FROM PERSON p
    INNER JOIN PERSON_RECORD pr
        ON (p.person_id = pr.person_id)

I'll get 2 rows back for the person with the name 'A' and 1 record back for 'B' but I don't care about the first row for person 'A' (from 2012-05-01). I have tried using distinct to get rid of duplicates (on the name) but I need a guarantee that I'm getting the most recent date for that particular name. Feel like I'm missing something obvious but I can't get it to click.

There is no column called `record_id` in your tables. Your query doesn't make sense. — Gordon Linoff, Dec 06 '19 at 11:42

score 0 · Answer 1 · answered Dec 06 '19 at 11:44

You seem to want the most recent record for a given NAME. If so, you can use a correlated subquery to match the dates for names:

SELECT pr.record_id, p.person_name,
FROM PERSON p INNER JOIN
     PERSON_RECORD pr
     ON p.person_id = pr.person_id
WHERE p.date = (SELECT MAX(p2.date)
                FROM PERSON p2
                WHERE p2.name = p.name
               );

score 0 · Answer 2 · answered Dec 06 '19 at 11:46

One option is to rank person rows per date:

SQL> with
  2  person (id, name, cdate) as
  3    (select 1, 'A', date '2012-05-01' from dual union all
  4     select 2, 'A', date '2012-05-02' from dual union all
  5     select 3, 'B', date '2012-05-04' from dual
  6    ),
  7  person_record (id, person_id, data) as
  8    (select 1, 1, 'my data 1' from dual union all
  9     select 2, 2, 'my data 2' from dual union all
 10     select 3, 3, 'my data 3' from dual
 11    ),
 12  ranking as
 13    (select id, name, cdate,
 14            row_number() over (partition by name order by cdate desc) rn
 15     from person
 16    )
 17  select pr.id, p.name, p.cdate
 18  from ranking p join person_record pr on p.id = pr.person_id
 19  where p.rn = 1;

        ID N CDATE
---------- - ----------
         2 A 2012-05-02
         3 B 2012-05-04

SQL>

score 0 · Answer 3 · answered Dec 06 '19 at 11:59

A person, identified by their name, can have multiple entries in the person table. You only want the row with the latest entry. In Oracle you can use KEEP LAST for this.

select *
from 
(
  select name, max(id) keep (dense_rank last order by date) as last_id
  from person
  group by name
) p 
left join person_record pr on pr.person_id = p.last_id
order by p.last_id;

(If the ID is always incrementing, you can simply use max(id) and neglect the date of course.)

Joining two tables and getting the most recent records for a given name

3 Answers3