Value Associated with the Maximum of another column
-
I have a table that has the following columns:
CaseID intCaseStatusHistoryID intStatus intEffectiveDate datetimeSome sample data would be:
(1, 4, 2, '2002-2-19')(1, 5, 2, '2002-2-19')(1, 7, 2, '2002-2-20')(1, 10, 9, '2002-2-20') (2, 14, 27, '2002-2-19')(2, 45, 42, '2002-3-19')(2, 37, 21, '2002-6-20')(2, 20, 19, '2002-6-20')What I need is the status associated with the highest date. Further, if there is more than one row with for that highest date, I need the status in the record with the highest CaseStatusHistoryID.
So if there were just these two cases, I would need a resultset that looked like this:
CaseID, Status(1, 9)(2, 21)There's no requirement that the highest CaseStatusHistoryID will correspond to the highest effective date, so I can't just grab the highest CaseStatusHistoryID. I tried putting something together using joins to derived tables, but I ended up with three subqueries, and even if it had worked, I thought there would have to be a better way.
Thanks for your help.
Mattie
-
I wrote it this way, but there are several ways to do it.
subqueries, and or dervied tables really are the only way to do these types of queries.
select *
from Testtable OT
join (
select Testtable.Caseid, max(CaseStatusHistoryID) as CaseStatusHistoryID
from Testtable
join (select CaseID, max(EffectiveDate) as EffectiveDate
from Testtable
group by CaseID) DT on Testtable.CaseID = DT.CaseID
and Testtable.EffectiveDate = DT.EffectiveDate
group by Testtable.Caseid
) T1 on ot.Caseid = T1.Caseid and OT.CaseStatusHistoryID = T1.CaseStatusHistoryID
-
Ray,
Thank you so much for your solution. I find subqueries more intuitive than joins, but making the additions that I needed to your query made me think about why everything you did was in there.
Mattie
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply