List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:June 18 2007 2:11pm
Subject:Re: Hiding columns used in GROUP BY and HAVING clauses
View as plain text  
In the last episode (Jun 18), Edward Kay said:
> From: Dan Nelson [mailto:dnelson@stripped]
> > In the last episode (Jun 15), Edward Kay said:
> > > I have a table of addresses. Each address is associated with a
> > > primary entity and a primary entity can have n different
> > > addresses. For each primary entity, one address is marked as the
> > > main address.
> > > 
> > > I need a query to return all addresses that are the only address
> > > associated with the primary entity but aren't marked as the main
> > > address.
> > > 
> > > 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.

-- 
	Dan Nelson
	dnelson@stripped
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