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@chezphil.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.