From: | Phil Rhoades <phil(at)pricom(dot)com(dot)au> |
---|---|
To: | Mike Ginsburg <mginsburg(at)collaborativefusion(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: A select DISTINCT query? - followup Q |
Date: | 2008-01-27 21:32:19 |
Message-ID: | 1201469539.9624.8.camel@prix.pricom.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)collaborativefusion(dot)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(at)pricom(dot)com(dot)au
From | Date | Subject | |
---|---|---|---|
Next Message | mljv | 2008-01-27 21:39:11 | Re: Very long execution time of "select nextval('..');" |
Previous Message | Tom Lane | 2008-01-27 20:23:47 | Re: A select DISTINCT query? - followup Q |