Обсуждение: 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
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 >
This works in oracle:
SELECT
aid, bidFROM
aidbidWHERE
aid < bidUNION
SELECT
bid, aidFROM
aidbidWHERE
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
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:
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
>
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))
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:
On Thu, Apr 24, 2008 at 3:43 PM, Rhys Stewart <rhys.stewart@gmail.com> wrote:Well, I would appreciate getting shorter replies that are to the point
> 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>
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?
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))
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:
Rhys Stewart wrote:PostGIS LINESTRINGs?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)
Like ST_AsEWKT()?
> and I have written a function to return atextual representationSurely, you don't mean ST_Distance()?
> so that I can easily identify lines with similarconfiguration. 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?
What do you mean by "similar configuration"?
Colin