Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2004
    Posts
    22

    Unanswered: 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?

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use min(bpdeptracking.status) in the SELECT list
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2004
    Posts
    22
    I'll give that a try. I appreciate the suggestion.

  4. #4
    Join Date
    Dec 2004
    Posts
    22
    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.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2004
    Posts
    22
    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?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •