How can a select match each value only once?

Question

I'm matching amount values from table1 and table2, tables having duplicates and different number of rows. Any amount can be matched only once (so not the way a standard select works) because total amount in the select for a table should remain the same as if I added all inserted values from that table.

CREATE TABLE table1 (
    table1_amount int
);
INSERT INTO table1 (table1_amount)
VALUES
    (22),
    (11),
    (35),
    (45),
    (45),
    (65),
    (22),
    (22),
    (878),
    (56);

CREATE TABLE table2 (
    table2_amount int
);
INSERT INTO table2 (table2_amount)
VALUES
    (324),
    (43),
    (34),
    (35),
    (22),
    (22),
    (12),
    (35),
    (6);

A standard select would return 6 rows matching the three "22" from table1 to the two "22" from table2 (so every "22" from table1 gets matched twice):

SELECT table1.table1_amount, table2.table2_amount
FROM table1 FULL OUTER JOIN table2 ON table1.table1_amount=table2.table2_amount;

table1_amount table2_amount
22 22
22 22
22 22
22 22
22 22

Now, I'd like to have only 2 matchings + 1 unmatched amount from table1 so:

table1_amount table2_amount
22 22
22 22
22 NULL

How can you do that? I'm indicating here SQL but any solution (Excel, Access) would be good.

Answer 1

Try this using Row_Number():

with cte1 as
(Select *, ROW_NUMBER() over (partition by table1_amount order by table1_amount) as ranking from table1),
 cte2 as
(Select *,ROW_NUMBER() over (partition by table2_amount order by table2_amount) as ranking from table2)
Select cte1.table1_amount, cte2.table2_amount from cte1 FULL OUTER JOIN cte2 on cte1.table1_amount = cte2.table2_amount and cte1.ranking = cte2.ranking