GROUP BY rule holding me up - HELP!

fstop · #1 (**permalink**) 12-15-04, 12:34

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?

Code:

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.matterid

r937 · #2 (**permalink**) 12-15-04, 17:13

use min(bpdeptracking.status) in the SELECT list

fstop · #3 (**permalink**) 12-16-04, 17:54

I'll give that a try. I appreciate the suggestion.

fstop · #4 (**permalink**) 12-17-04, 18:49

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.

Pat Phelan · #5 (**permalink**) 12-17-04, 23:17

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

r937 · #6 (**permalink**) 12-17-04, 23:31

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

fstop · #7 (**permalink**) 12-21-04, 17:41

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?

r937 · #8 (**permalink**) 12-21-04, 19:22

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')

Code:

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

Internet Services

Web Development

Digital Marketing

Consumer Tech