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

I'd say you need to rethink your schema.

This works in oracle:

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

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 Wed, Apr 23, 2008 at 1:37 PM, Scott Marlowe <scott.marlowe@gmail.com>
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?

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))

On Thu, Apr 24, 2008 at 5:43 PM, Scott Marlowe <scott.marlowe@gmail.com>
wrote:

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))

On Fri, Apr 25, 2008 at 12:07 PM, Colin Wetherbee <cww@denterprises.org>
wrote: