On 20 Jul 2012, at 18:15, Chris Angelico wrote: > On Sat, Jul 21, 2012 at 1:53 AM, Rich Shepard <rshep...@appl-ecosys.com> > wrote: >> The table has a Boolean indicator column with values of 0 or 1 for each >> row in the table and another attribute column for parameter names. I need to >> find all parameter names where the indicator value is only 0 for all rows of >> that parameter. At least some of the parameters have both rows with 0 and >> rows with 1 in the indicator attribute. I want to find all (any?) that have >> only zeros. > > Try this: > > SELECT DISTINCT param FROM table WHERE indicator=0 > EXCEPT > SELECT DISTINCT param FROM table WHERE indicator=1
I don't think the DISTINCT is necessary there, doesn't EXCEPT already return a distinct set, just like UNION (hence the existence of UNION ALL)? It can also be written as a correlated subquery: SELECT DISTINCT param FROM table t1 WHERE indicator = 0 AND NOT EXISTS (SELECT 42 FROM table t2 WHERE t2.param = t1.param AND indicator <> 0) (Where 42 is just some placeholder value because the syntax requires it, any value will do but NULL might throw a spanner in the wheels) Alban Hertroys -- The scale of a problem often equals the size of an ego. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
