Re: A select DISTINCT query? - followup Q

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