Hi,

result of my query has duplicates on one column like that:

id attr name fname
3 def Gerry gerry
3 en gerry gerry
1 ac fred fred


how do I eliminate a row with id 3 so that there is only one on result

i 've tried group by id, but then i get error that other fields must
apper in group clause.

Best regards, Michael

Michael <useitall (AT) gmail (DOT) com> wrote:
for your example:

test=# SELECT * from michael ;
id | attr | name | fname
----+------+-------+-------
3 | def | Gerry | gerry
3 | en | gerry | gerry
1 | ac | fred | fred
(3 Zeilen)

Zeit: 0,184 ms
test=*# delete from michael where ctid not in (select distinct on (id) ctid from michael);
DELETE 1
Zeit: 0,423 ms
test=*# SELECT * from michael ;
id | attr | name | fname
----+------+-------+-------
3 | def | Gerry | gerry
1 | ac | fred | fred
(2 Zeilen)


For the future: use a primary key ...


HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

On Jan 14, 7:41*pm, Andreas Kretschmer <akretsch... (AT) spamfence (DOT) net>
wrote:
Hi Andreas,
thanks for your suggestions.
I don't wont to delete data from table,because i need them all, only
output of query should not have any duplicates.

SELECT * FROM table where fname = 'gerry' and (attr = 'en' or attr =
'def') /* there are also other gerry in table with id3 but diffrent
attr */

Regards, Michael

Hi Michael, your last reply indicates that Andreas is very right on
strengthen the need for a primary key (over id, attr), otherwise you
will always fight with different "versions" of Your records, which one
to exclude in Your view? etc.
Kind Regards, Nico

Michael schrieb:

On Jan 14, 11:07*pm, Nico Latzer <n... (AT) mnet-online (DOT) de> wrote:
what is, if primary key is (id,attr)

Regards, Michael