Hi Steve, Does this help?
select * from (select gid,item,data, ROW_NUMBER() OVER (Partition by item order by gid) as subid from a) as t1 join (select gid,item,moredata, ROW_NUMBER() OVER (Partition by item order by gid) as subid from b) as t2 on t1.item=t2.item and t1.subid=t2.subid; Cheers Phil On Thu, Feb 19, 2015 at 3:06 AM, Stephen Woodbridge <wood...@swoodbridge.com > wrote: > I have an interesting join problem that I'm not sure how to tackle in sql. > > I have two tables that I need to join (well really match) like this: > > create table a ( > gid serial, > item text, > data text > ); > > create table b ( > gid serial, > item text, > moredata text > ); > > gid can be used to force correct ordering of the records. > item can be one or more rows with the same value in it (for example there > might be 10 item='foo' records) > data and more data is stuff related to the given record. > > What I know is that: > > 1. if table a has N rows for a given item then table b will have N rows > 2. that the N rows in table a and table b are in the same order by gid > 3. it is not safe to assume that a.gid=b.gid will link the correct records > > > What I need is to match/join is: > > a.item.row[1] to b.item.row[1] > a.item.row[2] to b.item.row[2] > ... > a.item.row[N] to b.item.row[N] > > where a.item=b.item > > Any thoughts on how to solve this with SQL? > > select aa.item, aa.cnt, bb.cnt > from (select item, count(*) as cnt from a group by item) aa > left outer join (select item, count(*) as cnt from b group by item) b > on aa.item=bb.item > order by aa.item; > > So likewise, we should be able to do something like: > > select aa.item, aa.data, bb.moredata > from (select item, data from a order by item, gid) aa > left outer join (select item, moredata from b order by item, gid) b > on aa.item=bb.item and a.itemrow=b.itemrow > order by aa.item; > > This needs some way of assigning itemrow numbers that can be matched. I > think this is an application of over() but I'm not quite sure how to apply > it. > > Thoughts would be appreciated :) > > Thanks, > -Steve > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
