[postgis-users] [OFF-TOPIC] SQL question

[Phil Bartie]

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 <woodbri at 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 at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150219/154fca65/attachment.html>