All we need is an easy explanation of the problem, so here it is.
I am trying to select unique record "pairs", where a pair is 2 rows that have equal and opposite numeric values (eg 10 and -10). Once a record has been paired, it can’t be used in another set of pairs. Consider this table:
CREATE TABLE vals ( id text, scalar numeric ); INSERT INTO vals (id, scalar) VALUES ('A', 10), ('B', -10), ('C', 10), ('D', -10), ('E', 10);
I have tried multiple variations of
DISTINCT ON (), and
UNIQUE, but the closest I’ve come so far is the following:
WITH all_matching AS ( SELECT v.id id1, v2.id id2 FROM vals v JOIN vals v2 ON v.scalar = (v2.scalar * -1) WHERE v.scalar > 0 ), unique_left_id AS ( SELECT DISTINCT ON (id1) * FROM all_matching ) SELECT * FROM unique_left_id;
id1 | id2 ----+---- A | B C | B E | B
The obvious problem is that the id
B is being used to pair off against all of the other transactions, when I only want to use it once. If I select distinct on
id2 after the final transaction, I would be left with a single pair instead of 2 pairs.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
WITH cte AS ( SELECT id, scalar, ROW_NUMBER() OVER (PARTITION BY scalar ORDER BY id) rn FROM vals ) SELECT t1.id, t2.id FROM cte t1 JOIN cte t2 ON t1.scalar = -t2.scalar AND t1.scalar > t2.scalar AND t1.rn = t2.rn
PS. This code does not process
scalar IN (0, NULL).
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂