1

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.

3

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.