From: | Harald Fuchs <hf0114x(at)protecting(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: A select DISTINCT query? - followup Q |
Date: | 2008-01-28 10:34:32 |
Message-ID: | pu63xeck7b.fsf@srv.protecting.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
In article <1201455192(dot)28880(dot)105(dot)camel(at)prix(dot)pricom(dot)com(dot)au>,
Phil Rhoades <phil(at)pricom(dot)com(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | mljv | 2008-01-28 11:19:11 | Re: Very long execution time of "select nextval('..');" |
Previous Message | Rainer Bauer | 2008-01-28 09:07:43 | Is news.postgresql.org down? |