Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
That worked, thanks! –  Daи Dec 11 '13 at 23:44
    
I don't see the point of the subquery on Company. That can just be the company table itself, no? –  Max Vernon Dec 11 '13 at 23:57
    
Any way to make it not show all the keys at the end, though? –  Daи 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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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