Re: A select DISTINCT query? - followup Q

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!