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