Thread: 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

I'd say you need to rethink your schema.

On Wed, Apr 23, 2008 at 12:11 PM, Rhys Stewart <rhys.stewart@gmail.com> wrote:
> 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
>

SELECT

aid, bid

FROM

aidbid

WHERE

aid < bid

UNION

SELECT

bid, aid

FROM

aidbid

WHERE

bid < aid

Rhys Stewart <rhys.stewart@gmail.com> wrote:

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

---

Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.

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

On 23/04/2008 20:33, Roberts, Jon wrote:

> create table ugly

[...snip...]

> create or replace function fn_ugly() returns setof ugly as

[...snip...]

>   create temporary table temp_ugly

[...snip...]

> select * from fn_ugly();

[...snip...]

Heh heh - I think we get the point! LOL :-)

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

> On 23/04/2008 20:33, Roberts, Jon wrote:
>
> > create table ugly
>
> [...snip...]
>
> > create or replace function fn_ugly() returns setof ugly as
>
> [...snip...]
>
> >   create temporary table temp_ugly
>
> [...snip...]
>
> > select * from fn_ugly();
>
> [...snip...]
>
> Heh heh - I think we get the point! LOL :-)
>

I was sitting there thinking, "what should I call this ugly table?"  :)


Jon

Roberts, Jon wrote:
>> On 23/04/2008 20:33, Roberts, Jon wrote:
>>
>>> create table ugly
>> [...snip...]
>>
>>> create or replace function fn_ugly() returns setof ugly as
>> [...snip...]
>>
>>> create temporary table temp_ugly
>> [...snip...]
>>
>>> select * from fn_ugly();
>> [...snip...]
>>
>> Heh heh - I think we get the point! LOL :-)
>>
>
> I was sitting there thinking, "what should I call this ugly table?"
> :)

I especially like fn_ugly().  It puts the icing on the cake, if you will. :)

Colin

While I thank you for your time in reading and responding, <minor rant follows> This world is not ideal at any level, be it the lack of financial equity, the petty prejudices that permeate societies on a whole, increasing gas and food prices worldwide (I've officially parked my car and am taking the bus until gas goes down.....as if that is going to happen), the apparent negligence of the atrocities in and around Sudan which continue to be ignored by world powers and the fact that last night I was terrorized by mosquitoes because my mosquito repellent is depleted.  Data and databases are by no means exempt. One does not always have the luxury of designing a schema from scratch. Some people, myself included, do not use databases in the traditional fashion (traditional being part of a application of some type). That being said, I would appreciate that any further questions I have not be responded to by single line emails extolling the virtues of properly designed schemata, normalization or the like.</minor rant precedes>

Yeah, I would have loved to have been able to have a schema to rethink, in fact i just got some data and need to extract some similar values in a column, but thanks for your time!!


Rhys

Peace & Love| Live Long & Prosper

On Thu, Apr 24, 2008 at 3:43 PM, Rhys Stewart <rhys.stewart@gmail.com> wrote:

> type). That being said, I would appreciate that any further questions I have
> not be responded to by single line emails extolling the virtues of properly
> designed schemata, normalization or the like.</minor rant precedes>

Well, I would appreciate getting shorter replies that are to the point
and don't rely on standing on soap boxes and using exercises in
polemics to make their point, but I probably won't get that.

The fact is, if your data is in that format, then the schema is
working against you, and everything you do is going to be much harder
than changing your schema to something that makes some more sense.

Next time I'll hold your hand a bit more, but yesterday I was very far
out of it (I'm not exactly 100% today either) with a bad head cold.
Now, should we have more exchanges to determine who can use the most
flowery of speech or should we talk pgsql and schema changes?

On Apr 24, 2008, at 5:43 PM, Scott Marlowe wrote:

> On Thu, Apr 24, 2008 at 3:43 PM, Rhys Stewart
> <rhys.stewart@gmail.com> wrote:
>
>> type). That being said, I would appreciate that any further
>> questions I have
>> not be responded to by single line emails extolling the virtues of
>> properly
>> designed schemata, normalization or the like.</minor rant precedes>
>
> Well, I would appreciate getting shorter replies that are to the point
> and don't rely on standing on soap boxes and using exercises in
> polemics to make their point, but I probably won't get that.
>
> The fact is, if your data is in that format, then the schema is
> working against you, and everything you do is going to be much harder
> than changing your schema to something that makes some more sense.
>
> Next time I'll hold your hand a bit more, but yesterday I was very far
> out of it (I'm not exactly 100% today either) with a bad head cold.
> Now, should we have more exchanges to determine who can use the most
> flowery of speech or should we talk pgsql and schema changes?

Perhaps his db-fu has yet to bud?

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

Erik Jones wrote:

>> Next time I'll hold your hand a bit more, but yesterday I was very far
>> out of it (I'm not exactly 100% today either) with a bad head cold.
>> Now, should we have more exchanges to determine who can use the most
>> flowery of speech or should we talk pgsql and schema changes?
>
> Perhaps his db-fu has yet to bud?

/me throws on some compost.

Joshua D. Drake

Indeed, I will endeavour to limit the length of my replies, although my extemporaneous nature, while ranting, will invariably result in prolonged discourse on my part . I am also grateful for your willingness to assist and saddened that you are ill. I do hope you recovery quickly.

The example I provided earlier was a very simplified model of the table I'm working with after a self join. The table in question contains geographic data (linestrings) and I have written a function to return a textual representation so that  I can easily identify lines with similar configuration. I want to now  select  lines that are in close proximity to each other and have the same configuration. So this requires a self join (afaik). So, short of a function like the one that was posited earlier, is there a method using just plain old sql to get the results I desire?

I trust the length and content of this reply is to your liking. :-)

The DDL is below and is followed by the self join that I used, if there is room for improvement wrt the format/shcema of the table I am open to suggestions.



Rhys

Peace & Love|Live Long & Prosper
-------------------------------
CREATE TABLE subsumed_secondary ( geom geometry,  id serial NOT NULL, CONSTRAINT subsumed_secondary_pkey PRIMARY KEY (id))

Rhys Stewart wrote:
> The example I provided earlier was a very simplified model of the table
> I'm working with after a self join. The table in question contains
> geographic data (linestrings)

PostGIS LINESTRINGs?

 > and I have written a function to return a
> textual representation

Like ST_AsEWKT()?

 > so that  I can easily identify lines with similar
> configuration. I want to now  select  lines that are in close proximity
> to each other and have the same configuration. So this requires a self
> join (afaik). So, short of a function like the one that was posited
> earlier, is there a method using just plain old sql to get the results I
> desire?

Surely, you don't mean ST_Distance()?

What do you mean by "similar configuration"?

Colin

Yes, PostGIS LINESTRINGS, no not ST_AsEWKT(). By similar configuration I mean shape. So I can identify two lines if they have a similar shape. No not ST_Distance()....and don't call me Shirley.

I also neglected to include the query in my previous post.

SELECT a.id,b.id FROM subsumed_secondary a
INNER JOIN subsumed_secondary b ON a.geom && b.geom AND a.id <> b.id
WHERE dev.line_descriptor_2(simplify(a.geom,1.5)) = dev.line_descriptor_2(simplify(b.geom,1.5))