What I thought would be a simple, single table select keeps eluding me.
I've looked in Rick van der Laans' book and the Joe Celko books here and
have not learned how to write the query.
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.
I have tried various flavors of this non-working syntax:
SELECT DISTINCT(param) from table WHERE indicator = 0 and indicator <> 1
order by param;
and have not found the correct way of writing this either directly or as a
correlated query. The proper syntax must not be complicated and I would
appreciate learning how to write it.
Rich
Comments
Re: Select Rows With Only One of Two Values
By Chris Angelico at 07/20/2012 - 12:15On Sat, Jul 21, 2012 at 1:53 AM, Rich Shepard <rshepard@appl-ecosys.com> wrote:
Try this:
SELECT DISTINCT param FROM table WHERE indicator=0
EXCEPT
SELECT DISTINCT param FROM table WHERE indicator=1
You'll get a list of rows with indicator 0, and then remove from that
list any that are also found in the second query. What's left is the
rows that have only indicator 0.
Chris Angelico
Re: Select Rows With Only One of Two Values
By Alban Hertroys at 07/20/2012 - 16:52I 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
Re: Select Rows With Only One of Two Values
By Chris Angelico at 07/20/2012 - 21:55On Sat, Jul 21, 2012 at 6:52 AM, Alban Hertroys < ... at gmail dot com> wrote:
Oops, yes. I usually use UNION ALL and friends, and IMHO that "ALL"
keyword is one of SQL's weirder warts. Most of the time, a wordier
query involves more work (SELECT DISTINCT vs SELECT), but in this odd
instance, it's the other way around.
And yes, it can. Not sure whether the subquery or EXCEPT notation is
more readable. Much of a muchness, really.
ChrisA
Re: Select Rows With Only One of Two Values [RESOLVED]
By Rich Shepard at 07/20/2012 - 12:21Chris,
Thank you. I knew it was simple, and I've not before used the EXCEPT
condition.
Very much appreciate,
Rich
Re: Select Rows With Only One of Two Values [RESOLVED]
By Chris Angelico at 07/20/2012 - 12:25On Sat, Jul 21, 2012 at 2:21 AM, Rich Shepard <rshepard@appl-ecosys.com> wrote:
No probs! SQL, like many other languages, allows you to combine its
primitives in some fairly complex ways. I don't know how hard it'd be
to make it work on Postgres, but here's an epic piece of SQL
awesomeness:
<a href="http://thedailywtf.com/Articles/Stupid-Coding-Tricks-The-TSQL-Madlebrot.aspx" title="http://thedailywtf.com/Articles/Stupid-Coding-Tricks-The-TSQL-Madlebrot.aspx">http://thedailywtf.com/Articles/Stupid-Coding-Tricks-The-TSQL-Madlebrot....</a>
ChrisA
Re: Select Rows With Only One of Two Values [RESOLVED]
By Thomas Kellerer at 07/20/2012 - 12:37Chris Angelico wrote on 20.07.2012 18:25:
That has already been done - and much cleaner I think ;)
<a href="https://wiki.postgresql.org/wiki/Mandelbrot_set" title="https://wiki.postgresql.org/wiki/Mandelbrot_set">https://wiki.postgresql.org/wiki/Mandelbrot_set</a>
Re: Select Rows With Only One of Two Values [RESOLVED]
By Andreas Kretschmer at 07/20/2012 - 12:33<a href="http://wiki.postgresql.org/wiki/Mandelbrot_set" title="http://wiki.postgresql.org/wiki/Mandelbrot_set">http://wiki.postgresql.org/wiki/Mandelbrot_set</a>
Andreas