GROUP BY rule holding me up - HELP!

I need to pull a new field (bpdeptracking.status) out of this existing query, but to include the field in the select I must also include it in the Group By. This changes the dynamic (and the output) of the query. So I need to figure out a way to keep the existing query intact and still obtain the bpdeptracking.status within the context of the parameters of this query. Can anyone help?

select min(calendarentries.entrydate) as firstdepo,prime.matterid
from dep join bpdeptracking on dep.depid=bpdeptracking.depid
join calendarentries on bpdeptracking.calendarid = calendarentries.calendarid
join depmaps on depmaps.depid = dep.depid
join ( select * from BP_ActivebyPara ) as prime on prime.matterid = depmaps.uniqueid
where calendarentries.entrydate <> '11/11/1911'
and (dep.deptype = 'P' or dep.deptype = 'P/IP')
and bpdeptracking.status in ('Concluded','Continued')
group by prime.matteriduse min(bpdeptracking.status) in the SELECT list|||I'll give that a try. I appreciate the suggestion.|||Unfortunately, "use min(bpdeptracking.status) in the SELECT list" won't work because it returns the minimum value from bpdeptracking.status during the GROUP BY operation. As an example, the GROUP BY field is prime.matterid. If there are 10 rows that are identical based on this field, it will group them together and return the row that meets the min(calendarentries.entrydate) criteria. If I use min(bpdeptracking.status), it will return the minimum value for that column from the 10 rows in the group. I don't want the minimum value, I want the value in the row that is selected by the GROUP BY field using the min(calendarentries.entrydate) criteria.

So I will probably have to use 2 queries unless someone on this forum has an idea on how to accomplish this more efficiently.|||The use of GROUP BY produces an aggregate, it doesn't select any individual row. The two ideas are mutually exclusive, you can only have one or the other, never both in a single query.

By doing some creative "digital dancing", you can make it appear that you are getting a single row. If you can give us some sample data, and the results you'd like to see from that data, then I'd bet that we can help you to conjour it up!

-PatP|||if bpdeptracking.status is identical for every row in each prime.matterid group, then you should be able to take the min or the max, and it will be the same regardless

if it isn't the same for every row in each prime.matterid group, then either you (a) include it in the GROUP BY, which you say you can't do because it "changes the dynamic", or (b) pick any arbitrary value for bpdeptracking.status within each group (which is exactly what my choice of min does), or (c) realize that you cannot do what you are asking to do|||bpdeptracking.status is NOT identical for every row in each prime.matterid group, which is why I can't use MIN or MAX.

For example: say the DB has a group of 10 identical matterid values, each with a different entrydate. When I group those together by matterid and take the MIN(entrydate), I want the actual value of bpdeptracking.status that relates to the MIN(entrydate) for the prime.matterid group. If I use MIN(bpdeptracking.status), it returns the minimum alpha value for bpdeptracking.status from the 10 rows rather than the actual value in the selected row.

Is there a way to select bpdeptracking.status using the query below perhaps as a subquery or as 2 seperate queries to produce the desired result?|||aha! now we are getting someplace! :)

there may still be a problem, because if you want the row with the min entrydate, it may not be related to a status in ('Concluded','Continued')

select prime.matterid
, bpdeptracking.status
, c1.entrydate as firstdepo
from dep
join bpdeptracking
on dep.depid
= bpdeptracking.depid
join calendarentries as c1
on bpdeptracking.calendarid
= c1.calendarid
join depmaps
on dep.depid
= depmaps.depid
join BP_ActivebyPara as prime
on depmaps.uniqueid
= prime.matterid
where c1.entrydate <> '11/11/1911'
and c1.entrydate
= ( select min(entrydate)
from calendarentries
where calendarid
= bpdeptracking.calendarid
and entrydate <> '11/11/1911' )
and dep.deptype in ('P', 'P/IP')
and bpdeptracking.status in ('Concluded','Continued')
group
by prime.matterid
, bpdeptracking.status