List:General Discussion« Previous MessageNext Message »
From:Edward Kay Date:June 18 2007 3:15pm
Subject:RE: Hiding columns used in GROUP BY and HAVING clauses
View as plain text  

> -----Original Message-----
> From: Baron Schwartz [mailto:baron@stripped]
> Sent: 18 June 2007 15:53
Sent: 18 June 2007 15:11
> >
> > <snip>
> >
> >>>>> At the moment, I have this and it works:
> >>>>>
> >>>>> 	select * from contact_address
> >>>>> 	group by primary_entity_id
> >>>>> 	having count(primary_entity_id) = 1
> >>>>> 	and is_primary = 0;
> >>>>>
> >>>>> This is fine except I want to use the result in a sub-query.
> >>>>> Since it returns two columns this doesn't work:
> >>>>>
> >>>>> 	update contact_address set is_primary = 1 where
> >> address_id in (
> >>>>> 		select * from contact_address
> >>>>> 		group by primary_entity_id
> >>>>> 		having count(primary_entity_id) = 1
> >>>>> 		and is_primary = 0
> >>>>> 	);
> >>>>>
> >>>>> Normally, I'd only return the address_id in the sub-SELECT, but
> I
> >>>>> need the is_primary column for the HAVING clause.
> >>>> I did some tests, and it looks like you can use aggregate functions
> >>>> in your HAVING clause without actually selecting the column.  So
> >>>> "HAVING COUNT(primary_entity_id) = 1" should work even if you only
> >>>> select address_id.
> >>> Yes, that is true and it does work.
> >>>
> >>> What doesn't work however, is the extra 'AND is_primary = 0' HAVING
> >>> clause.
> >> Couldn't you move that up into a WHERE clause (still in the subquery)?
> >> It's just a regular field comparison, so it doesn't have to be in the
> >> HAVING clause.
> >
> > No, because that's not the same thing. Imagine the following data:
> >
> > address_id   primary_entity_id   is_primary
> > -------------------------------------------
> >   1            293                 0
> >   2            293                 1
> >
> >
> > With my query above, the sub-query result set would be empty since
> > count(primary_entity_id) = 2.
> >
> > If I move the is_primary = 0 requirement to a WHERE clause,
> then the first
> > row would be incorrectly updated since the group function would only be
> > working on the sub-set of data (where is_primary = 0).
> >
> > It's a subtle but important difference.
>
> Then I think what you really want in your HAVING clause is "AND
> MIN(is_primary)
> = 0".
>
> Baron

But I'm first grouping by primary_entity_id and then only selecting the rows
where count(primary_entity_id) = 1, so is_primary clause will only ever be
working on one value.

Edward

Thread
Hiding columns used in GROUP BY and HAVING clausesEdward Kay15 Jun
  • Re: Hiding columns used in GROUP BY and HAVING clausesBaron Schwartz15 Jun
  • Re: Hiding columns used in GROUP BY and HAVING clausesDan Nelson15 Jun
    • RE: Hiding columns used in GROUP BY and HAVING clausesEdward Kay18 Jun
      • Re: Hiding columns used in GROUP BY and HAVING clausesDan Nelson18 Jun
        • RE: Hiding columns used in GROUP BY and HAVING clausesEdward Kay18 Jun
          • Re: Hiding columns used in GROUP BY and HAVING clausesBaron Schwartz18 Jun
            • RE: Hiding columns used in GROUP BY and HAVING clausesEdward Kay18 Jun
          • Re: Hiding columns used in GROUP BY and HAVING clausesDan Nelson18 Jun
            • Re: Hiding columns used in GROUP BY and HAVING clausesBaron Schwartz18 Jun
              • RE: Hiding columns used in GROUP BY and HAVING clausesEdward Kay19 Jun