Re: query question really cant give a summary here so read the body ; -)

You really don't have duplicate data and you should redesign your table structure. However, here is a way to do it.

create table ugly
(aid integer, bid integer);

insert into ugly (aid, bid) values (1,5);
insert into ugly (aid, bid) values (2,6);
insert into ugly (aid, bid) values (3,7);
insert into ugly (aid, bid) values (4,9);
insert into ugly (aid, bid) values (5,1);
insert into ugly (aid, bid) values (6,2);
insert into ugly (aid, bid) values (7,3);
insert into ugly (aid, bid) values (8,10);
insert into ugly (aid, bid) values (9,4);
insert into ugly (aid, bid) values (10,8);

create or replace function fn_ugly() returns setof ugly as
$$
declare
v_rec ugly;
v_rec2 ugly;
begin

create temporary table temp_ugly
(aid integer, bid integer) on commit drop;

for v_rec in select * from ugly loop

if not exists (select null
from temp_ugly
where v_rec.aid = bid
and v_rec.bid = aid) then
insert
into temp_ugly
values (v_rec.aid, v_rec.bid);

end if;

end loop;

for v_rec2 in select * from temp_ugly loop

return next v_rec2;

end loop;

end;
$$
language 'plpgsql';

select * from fn_ugly();

Jon

________________________________________
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Rhys Stewart
Sent: Wednesday, April 23, 2008 1:12 PM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] query question really cant give a summary here so read the body ;-)

Hi all,

have the following table

aid    | bid
--------------
1    |5
2    |6
3    |7
4    |9
5    |1
6    |2
7    |3
8    |10
9    |4
10  |8
both aid & bid represent the same data in another table, but the table has duplicate data and i did a self-join to get the id's out. The question is how do i get a distinct listing between both columns

like
aid | bid
-----------
1|5
2|6
3|7
4|9
10|8

Have been racking my brain for the past hour....any suggestions?

Thanks
Rhys