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.