Select Most Recently Updated Row Based on Sequence ID with Three Tables 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&mdash;not Company.
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.