Join two tables, each with multiple rows with the same key values on just the latest version of each

Question

I'm trying to list the codes and descriptions that are used in a "person" table. There are many codes in the "code_table" but I don't want to list unused codes.

I have a "person" table that has a record for each time a person is changed... in particular, I'm interested in the "person_code" field. Every person record has a date_revised value that indicates when the change was made. The person table has an identity primary key (person_id) and a person identifier (person_int) for each person.

I have a "code_table" which has records for each time it's changed, again containing a date_revised field. It has a "code_type" field that matches person_code in the person table and "code_data" which is the description of the code. Code_table also has a identity primary key and an identifier (code_group + code_type)

So, the data might look like: person:

person_id person_int person_code person_first person_last date_revised
1 1234 JPM John Doe 1/2/2021
232 1234 JPM Johnathan Doe 2/11/2021
432 1234 PM Johnny Doe 4/24/2021

code_table:

code_id code_group code_type code_description date_revised
2 person_code PM Project Manager 1/9/2021
32 person_code PM Senior Project Manager 3/16/2021

So, I'm looking for the "PM" and "Senior Project Manager" code to be listed. If there were only one row for each, I could simply join them with:

select distinct code_type,code_data from person 
join code_table on code_group='person_code' and person_code = code_type  
order by code_data

Obviously, I get a ton of duplicates. So, how do I write a query so that I link the latest person record with the latest code_table record and list the latest code_table values? The result should show only the unique code_table entries.

EDIT: I've never used db fiddle and didn't see how to save it. Here are the statements to create a sample:

create table person (
person_id int,
person_int int, person_code varchar(55),
person_first varchar(55),
person_last varchar(55),
date_revised datetime)
insert into person select 1, 1234, 'JPM', 'John', 'Doe', '1/2/2021'
insert into person select 12, 1234, 'JPM', 'Johnathn', 'Doe', '2/2/2021'
insert into person select 2231, 1234, 'PM', 'Johnny', 'Doe', '2/2/2021'
insert into person select 2232, 222, 'PM', 'Billy', 'Bob', '2/2/2021'
select * from person
create table code_table (
code_id int,
code_group varchar(55),
code_type  varchar(55),
code_description  varchar(55),
date_revised datetime)
insert into code_table select 1, 'person_code', 'JPM', 'Junior Project Manager', '1/9/2021'
insert into code_table select 2, 'person_code', 'PM', 'Project Manager', '1/9/2021'
insert into code_table select 3, 'person_code', 'MGR', 'Manager', '1/9/2021'
insert into code_table select 32, 'person_code', 'PM', 'Senior Project Manager', '2/9/2021'
insert into code_table select 33, 'person_code', 'SUP', 'Supervisor', '2/19/2021'
select * from code_table

The only thing that should result is "PM Senior Project Manager"

Answer 1

Assuming that the date_revised will be unique per person, I think the following code will work with you:

select distinct
    c.code_type,
    c.code_data 
from person p with(nolock)
inner join code_table c on c.code_group='person_code' and p.person_code = c.code_type  
inner join (
    select person_int, max(date_revised) date_revised from person a with(nolock) group by a.person_int
    ) x on x.date_revised = p.date_revised and x.person_int = p.person_int
order by c.code_data

Answer 2

I was thinking that this gets really complex. What would be wrong with creating two views that only select the latest rows and then use a regular join to get the data?

create view view_code_table 
as 
WITH ct AS 
(select *, ROW_NUMBER() OVER (PARTITION BY code_group,code_seq,code_type,code_language ORDER BY date_revised DESC) AS rn from code_table) 
SELECT * FROM ct WHERE rn = 1

create view view_person
as 
WITH p AS 
(select *, ROW_NUMBER() OVER (PARTITION BY org_int,person_int ORDER BY date_revised DESC) AS rn from person) 
SELECT * FROM p WHERE rn = 1

Then join the views with whatever constraints you want.

select distinct code_type,code_data from view_person 
join view_code_table on code_group='person_code' and person_code = code_type  
order by code_data