Thanks in advance for you help.
SQL Server 2000
I have a complex join, but a simple example will do the trick so...
table1 (placement records, possibly many per case,highest ID being
most recent)
----------
placementID(pk) * CaseID(fk) * OrganizationID(fk) * Name * Number
table2 (Organizations Table, Many Placements can be at 1 organization)
----------
OrganizationID(pk) * OrgName * OrgType
table 3(Case Table, each Case can have many placements)
----------
CaseID(pk) * StaffName * CreationDate
now my query...
SELECT t1.placementID,t1.caseID,t2.OrgName
FROM table1 as t1
INNER JOIN table2 as t2 on t2.OrganizationID = t1.OrganizationID
WHERE exists (select distinct max(placementID),CaseID
from t1 group by caseID)
GROUP BY t2.OrgName,t1.PlacementID,t1.CaseID
my results
-------------
placementID CaseID OrgName
1 1 oneOrg
2 1 two org
3 1 three org
4 2 another org
5 3 yet another org
my desired results
------------------
PlacementID CaseID OrgName
3 1 three org
4 2 another org
5 3 yet another org
as you can see i get all records, but what i want is to see only the
last placementID for each case so i dont want duplicate caseID but I
do need the orgName, and yes the query works correctly without the org
name but as soon as i add orgName to the select statement I get
duplicate CaseID's, How do i eliminate duplicate CaseID's and get only
the MAX(placementID) for each Case and the OrgName
please advise, getting desperate.
thanks again so much for the help
