Thread: Re: A select DISTINCT query? - followup Q
People, > select count(*) as cnt, name from tst group by name having count(*) = 1 This worked for my basic example but not for my actual problem - I get "column comment must appear in the GROUP BY clause or be used in an aggregate function" errors so I have a related question: With table: name comment 1 first comment 2 second comment 3 third comment 3 fourth comment 4 fifth comment 5 sixth comment - how can I use something like the previous select statement but where the comment field does not appear in the "group by" clause and gives the following result: 1 first comment 2 second comment 4 fifth comment 5 sixth comment Thanks, Phil. -- Philip Rhoades Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275) GPO Box 3411 Sydney NSW 2001 Australia Fax: +61:(0)2-8221-9599 E-mail: phil@pricom.com.au
Hi Phil, Each of columns that you specify in your SELECT clause, must also appear in the GROPU BY clause. SELECT COUNT(*) AS cnt, name, comment, ... FROM tst GROUP BY name, comment, ... HAVING COUNT(*) = 1; Phil Rhoades wrote: > People, > > > >> select count(*) as cnt, name from tst group by name having count(*) = 1 >> > > > This worked for my basic example but not for my actual problem - I get > "column comment must appear in the GROUP BY clause or be used in an > aggregate function" errors so I have a related question: > > With table: > > name comment > > 1 first comment > 2 second comment > 3 third comment > 3 fourth comment > 4 fifth comment > 5 sixth comment > > - how can I use something like the previous select statement but where > the comment field does not appear in the "group by" clause and gives the > following result: > > 1 first comment > 2 second comment > 4 fifth comment > 5 sixth comment > > Thanks, > > Phil. > Mike Ginsburg Collaborative Fusion, Inc. mginsburg@collaborativefusion.com 412-422-3463 x4015 -- **************************************************************** IMPORTANT: This message contains confidential information and is intended only for the individual named. If the reader of this message is not an intended recipient (or the individual responsible for the delivery of this message to an intended recipient), please be advised that any re-use, dissemination, distribution or copying of this message is prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. ****************************************************************
On Sunday 27 January 2008 10:56:18 am Mike Ginsburg wrote: > Hi Phil, > Each of columns that you specify in your SELECT clause, must also > appear in the GROPU BY clause. > > SELECT COUNT(*) AS cnt, name, comment, ... > FROM tst > GROUP BY name, comment, ... > HAVING COUNT(*) = 1; > Is the requirement of select fields matching group by fields a SQL92 requirement or something to due to Postgres? I ask because with Visual Fox Pro I know that I can have several select fields with only one group by field. -- John Fabiani
johnf <jfabiani@yolo.com> writes:
> On Sunday 27 January 2008 10:56:18 am Mike Ginsburg wrote:
>> Each of columns that you specify in your SELECT clause, must also
>> appear in the GROPU BY clause.
> Is the requirement of select fields matching group by fields a SQL92
> requirement or something to due to Postgres? I ask because with Visual Fox
> Pro I know that I can have several select fields with only one group by
> field.
It is in fact a SQL92 requirement: section 7.9 <query specification> saith
7) If T is a grouped table, then each <column reference> in each
<value expression> that references a column of T shall refer-
ence a grouping column or be specified within a <set function
specification>.
(A "set function" is what PG calls an aggregate function.)
Later versions of the spec relax that a bit: in SQL99, if you GROUP BY
a primary key (or some other cases that are not too interesting in
practice) then there can be only one row per group anyway and so
references to other columns will have well-defined values. We have not
got around to implementing that extension.
I don't know FoxPro, but there are some DBMSes (cough m***l cough) that
simply let you reference ungrouped columns without any check to see
whether what you have written is sensible or not. The results you get
from such a query are pretty unpredictable, or at least implementation-
dependent.
regards, tom lane
Mike, I can't do that with my comments - I get all six of the records in the result with the example instead of just four like I want . . but someone else had a solution without using the "group by" clause . . Phil. On Sun, 2008-01-27 at 13:56 -0500, Mike Ginsburg wrote: > Hi Phil, > Each of columns that you specify in your SELECT clause, must also > appear in the GROPU BY clause. > > SELECT COUNT(*) AS cnt, name, comment, ... > FROM tst > GROUP BY name, comment, ... > HAVING COUNT(*) = 1; > > > Phil Rhoades wrote: > > People, > > > > > > > >> select count(*) as cnt, name from tst group by name having count(*) = 1 > >> > > > > > > This worked for my basic example but not for my actual problem - I get > > "column comment must appear in the GROUP BY clause or be used in an > > aggregate function" errors so I have a related question: > > > > With table: > > > > name comment > > > > 1 first comment > > 2 second comment > > 3 third comment > > 3 fourth comment > > 4 fifth comment > > 5 sixth comment > > > > - how can I use something like the previous select statement but where > > the comment field does not appear in the "group by" clause and gives the > > following result: > > > > 1 first comment > > 2 second comment > > 4 fifth comment > > 5 sixth comment > > > > Thanks, > > > > Phil. > > > > Mike Ginsburg > Collaborative Fusion, Inc. > mginsburg@collaborativefusion.com > 412-422-3463 x4015 > -- Philip Rhoades Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275) GPO Box 3411 Sydney NSW 2001 Australia Fax: +61:(0)2-8221-9599 E-mail: phil@pricom.com.au
In article <1201455192.28880.105.camel@prix.pricom.com.au>, Phil Rhoades <phil@pricom.com.au> writes: > People, >> select count(*) as cnt, name from tst group by name having count(*) = 1 > This worked for my basic example but not for my actual problem - I get > "column comment must appear in the GROUP BY clause or be used in an > aggregate function" errors so I have a related question: > With table: > name comment > 1 first comment > 2 second comment > 3 third comment > 3 fourth comment > 4 fifth comment > 5 sixth comment > - how can I use something like the previous select statement but where > the comment field does not appear in the "group by" clause and gives the > following result: > 1 first comment > 2 second comment > 4 fifth comment > 5 sixth comment If you want to select both columns, but have uniqueness over the first only, you can use a derived table: SELECT tbl.name, tbl.comment FROM tbl JOIN (SELECT name FROM tbl GROUP BY name HAVING count(*) = 1) AS t ON t.name = tbl.name
"Harald Fuchs" <hf0114x@protecting.net> writes: > If you want to select both columns, but have uniqueness over the first > only, you can use a derived table: > > SELECT tbl.name, tbl.comment > FROM tbl > JOIN (SELECT name FROM tbl GROUP BY name HAVING count(*) = 1) AS t > ON t.name = tbl.name > Or use the first() aggregate since you know there's only going to be one anyways: select name, first(comment) from tbl group by name having count(*) = 1 -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!