Select Most Recently Updated Row Based on Sequence ID with Three Tables

Question

I've previously asked how to use a sequence ID to select the most recently updated row, but I'm not quite sure how to apply it to a more advanced query involving multiple joins. Here is my current query:

select
 p.Person_ID,
 p.Person_Name,
 p.Person_Age,
 d.Department_Name,
 c.Company_Name,
 c.Company_City,
 c.Company_State
from Person p
left join Department d 
 on p.Person_Department_ID=d.Department_ID
left join (
 select Company_ID, Company_Name, Company_City, Company_State
 from Company
 group by Company_ID, Company_Name, Company_City, Company_State
) as c on c.Company_ID=d.Department_Company_ID
where c.Company_ID=1

I have made an example on SQLFiddle for this question. I want to only select the most recent Person (max(Person_Seq_ID)), and only show the most up-to-date Department name next to that Person (max(Department_Seq_ID)). There should only be seven records returned in my uploaded example. How do I do this?

Note that only Person and Department have sequence IDs—not Company.

Answer 1

Unless I missed something, the following will give you what is needed :

SELECT * FROM 
(
select
 p.Person_ID,
 p.Person_Name,
 p.Person_Age,
 d.Department_Name,
 c.Company_Name,
 c.Company_City,
 c.Company_State,
 p.Person_Seq_ID,
 d.Department_ID,
 d.Department_Seq_ID,
 MAX(p.Person_Seq_ID) OVER(PARTITION BY p.Person_ID) as mPerson,
 MAX(d.Department_Seq_ID) OVER(PARTITION BY p.Person_ID, d.Department_ID) as mDep
from Person p
left join Department d 
 on p.Person_Department_ID=d.Department_ID
left join (
 select Company_ID, Company_Name, Company_City, Company_State
 from Company
 group by Company_ID, Company_Name, Company_City, Company_State
) as c on c.Company_ID=d.Department_Company_ID
where c.Company_ID=1
 ) a 
WHERE Person_Seq_ID = mPerson and Department_Seq_ID = mDep

Here is an updated link showing this on SQLFiddle.

• Hannah Vernon: I don't see the point of the subquery on Company. That can just be the company table itself, no?
• Dan: Any way to make it not show all the keys at the end, though?
• a1ex07: @Max Vernon : You are right , but I didn't pay attention to further optimization - focused on returning desired output.
• a1ex07: @Dan : Absolutely, you can select any columns inside subquery(derived table)