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

On 22 Apr 2004 17:09:24 -0700, Dave wrote:

>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

Hi Dave,

Try this:

SELECT t1.placementID,t1.caseID,t2.OrgName
FROM table1 as t1
INNER JOIN table2 as t2 on t2.OrganizationID = t1.OrganizationID
WHERE t1.placementID = (select max(t1b.placementID)
from table1 as t1b
where t1b.caseID = t1.caseID)

(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)