Re: Limitting full join to one match

John W Higgins wrote:
> On Wed, Dec 5, 2018 at 4:34 PM Phil Endecott <
> spam_from_pgsql_lists(at)chezphil(dot)org> wrote:
>
>> Dear Experts,
>>
>> I have a couple of tables that I want to reconcile, finding rows
>> that match and places where rows are missing from one table or the
>> other:
>>
>> ...
>
>
>> So my question is: how can I modify my query to output only two rows,
>> like this:?
>>
>> +------------+--------+------------+--------+
>> | date | amount | date | amount |
>> +------------+--------+------------+--------+
>> | 2018-01-01 | 10.00 | 2018-01-01 | 10.00 |
>> | 2018-02-01 | 5.00 | | |
>> | | | 2018-03-01 | 8.00 |
>> | 2018-04-01 | 5.00 | 2018-04-01 | 5.00 |
>> | 2018-05-01 | 20.00 | 2018-05-01 | 20.00 | 1
>> | 2018-05-01 | 20.00 | 2018-05-01 | 20.00 | 2
>> +------------+--------+------------+--------+
>>
>>
> Evening Phil,
>
> Window functions are your friend here. I prefer views for this stuff - but
> subqueries would work just fine.
>
> create view a_rows as (select *,
> row_number() OVER (PARTITION BY date, amount) AS pos
> from a);
> create view b_rows as (select *,
> row_number() OVER (PARTITION BY date, amount) AS pos
> from b);
>
> select
> a_rows.date,
> a_rows.amount,
> a_rows.pos,
> b_rows.date,
> b_rows.amount,
> b_rows.pos
> from
> a_rows full join b_rows using (date,amount,pos);

Thanks John, that's great. I'm a little surprised that there isn't an
easier way, but this certainly works.

Regard, Phil.