Re: A select DISTINCT query? - followup Q

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