>Hoping someone can guide me in a way to rewrite my query to get the desired results. I'm using SQL Server 2000. I'm bringing back a record set (example 1) that is based off meetingnumber, cardassigned. A meeting record might have multiple cards assigned (meetproc). This current query brings back everything and is correct. The desired result is to bring back, when there are multiple matching records with the same InvID and Meetingnumber, to only bring back the record (meetingnumber & InvID) with the highest PlanQty for that meetingnumber. This example is showing only one case (meetingnumber) but the query actually brings back all cases based off a date range. Example two is the desired result. Sorry for the length of this, but I've included the currently query at the bottom.
>
>
>

>meetingnumber CardAssigned InvID       PlanQty     HaveAvailQty
>------------- ------------ ----------- ----------- ------------
>27338         220          230         1           1
>27338         224          230         1           1
>27338         224          670         1           1
>27338         220          670         1           1
>27338         224          977         1           0
>27338         220          977         1           0
>27338         220          1013        5           5
>27338         224          1013        5           5
>27338         224          3038        2           2
>27338         220          3038        2           2
>=========
>* This next 2 records are an example where the same item has a variance
>* on the PlanQty, and I only want to get the record with the highest
>* PlanQty
>=========
>27338         224          3677        1           0
>27338         220          3677        0           1
>

>
>
>Desired result set
>
>

>meetingnumber CardAssigned InvID       PlanQty     HaveAvailQty
>------------- ------------ ----------- ----------- ------------
>27338         220          230         1           1
>27338         220          670         1           1
>27338         220          977         1           0
>27338         220          1013        5           5
>27338         220          3038        2           2
>27338         224          3677        1           0
>

>
>Current Query
>

>SELECT  dbo.meetings.meetingnumber,
>	dbo.meetproc.CardAssigned,
>	dbo.coSuper2.InvID,
>	dbo.coSuper2.PlanQty,
>	dbo.coSuper2.HaveAvailQty
>FROM    dbo.meetings inner join
>	dbo.meetproc on dbo.meetings.meetingnumber = dbo.meetproc.meetingnumber inner join
>	dbo.coSuper2 on dbo.meetproc.CardAssigned = dbo.coSuper2.coSuperID
>WHERE   (dbo.meetings.iscancelled = 0) AND (dbo.meetings.iscompleted = 0)
>and     dbo.meetings.RoomClosed=0
>and     dbo.meetings.isblock=0
>and     dbo.meetings.begintime between @ttStartDate and @ttEndDate
>and     dbo.meetings.DeptID=@tiDeptID
>and     dbo.meetproc.CardAssigned>0
>and     dbo.coSuper2.isactive=1
>order by dbo.meetings.meetingnumber,InvID
>

>
>
>Thanks in advance

SELECT  dbo.meetings.meetingnumber,
>	dbo.meetproc.CardAssigned,
>	dbo.coSuper2.InvID,
>	dbo.coSuper2.PlanQty,
>	dbo.coSuper2.HaveAvailQty
>FROM    dbo.meetings inner join
>	dbo.meetproc on dbo.meetings.meetingnumber = dbo.meetproc.meetingnumber inner join
>	dbo.coSuper2 on dbo.meetproc.CardAssigned = dbo.coSuper2.coSuperID
>WHERE   (dbo.meetings.iscancelled = 0) AND (dbo.meetings.iscompleted = 0)
>and     dbo.meetings.RoomClosed=0
>and     dbo.meetings.isblock=0
>and     dbo.meetings.begintime between @ttStartDate and @ttEndDate
>and     dbo.meetings.DeptID=@tiDeptID
>and     dbo.meetproc.CardAssigned>0
>and     dbo.coSuper2.isactive=1
inner join (SELECT  dbo.meetings.meetingnumber,
>	dbo.meetproc.CardAssigned,
>	dbo.coSuper2.InvID,
>	max(dbo.coSuper2.PlanQty) as MaxNumber,
>	FROM    dbo.meetings inner join
>	dbo.meetproc on dbo.meetings.meetingnumber = dbo.meetproc.meetingnumber inner join
>	dbo.coSuper2 on dbo.meetproc.CardAssigned = dbo.coSuper2.coSuperID
>WHERE   (dbo.meetings.iscancelled = 0) AND (dbo.meetings.iscompleted = 0)
>and     dbo.meetings.RoomClosed=0
>and     dbo.meetings.isblock=0
>and     dbo.meetings.begintime between @ttStartDate and @ttEndDate
>and     dbo.meetings.DeptID=@tiDeptID
>and     dbo.meetproc.CardAssigned>0
>and     dbo.coSuper2.isactive=1
group by 1,2,3)
) InnerSQL on dbo.Meetings.MeetingNumber = InnerSQL.MeetingNumber 
 and dbo.meetproc.CardAssigned = InnerSQL.CardAssigned AND
	dbo.coSuper2.InvID = InnerSQL.InvID AND
	dbo.coSuper2.PlanQty = InnerSQL.MaxNumber
order by dbo.meetings.meetingnumber,dbo.coSuper2.InvID