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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Rhys Stewart
Sent: Wednesday, April 23, 2008 1:12 PM
To: pgsql-general@xxxxxxxxxxxxxx
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
