Value Associated with the Maximum of another column

Question

Post reply

Value Associated with the Maximum of another column

MattieNH

SSCertifiable

Points: 7463
More actions
January 26, 2006 at 9:31 am

#92866

I have a table that has the following columns:
CaseID int
CaseStatusHistoryID int
Status int
EffectiveDate datetime
Some 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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

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

MattieNH SSCertifiable Points: 7463 More actions · Answer 2

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