find rows where there is another row with an opposite value in the table

Question

Im trying to find an efficient way to solve the problem:

I need to find all rows in a table where there is another row with an opposite column value.

For example I have transactions with columns id and amount

| id | amount |
|----|--------|
| 1  | 1      |
| 2  | -1     |
| 3  | 2      |
| 4  | -2     |
| 5  | 3      |
| 6  | 4      |
| 7  | 5      |
| 8  | 6      |

The query should return only the first 4 rows:

| id | amount |
|----|--------|
| 1  | 1      |
| 2  | -1     |
| 3  | 2      |
| 4  | -2     |

My current solution is terribly efficient as I am going through 1000's of transactions:

transactions.find_each do |transaction|
  unless transactions.where("amount = #{transaction.amount * -1}").count > 0
    transactions = transactions.where.not(amount: transaction.amount).order("@ amount DESC")
  end
end
transactions

Are there any built in Rails or Postgresql functions that could help with this?

I am a newbie. But I have not understood why you chose the 4 first lines. Can you explain ? — Maxence, Feb 15 '16 at 23:44
@Maxence each row in those 4 of have another row in the table with an opposite value. `-1` is opposite of `1` for example. — Deekor, Feb 15 '16 at 23:47
And you don't need to pair them explicitly? just have them as a batch? (lets say 10 rows have value of 1 and 45 rows have values of -1, you want the 55 rows alltogether?) — Maxence, Feb 15 '16 at 23:49
Try to find the unique values instead. like transactions.abs.uniq (am noob, but this seems good logic). Not sure if can work as uniq seems to work on arrays only.. check this post too http://stackoverflow.com/questions/9658881/rails-select-unique-values-from-a-column — Maxence, Feb 15 '16 at 23:53

score 0 · Answer 1 · answered Feb 16 '16 at 00:09

0

Use following query:

SELECT DISTINCT t1.* 
  FROM transactions t1 
  INNER JOIN transactions t2 ON t1.amount = t2.amount * -1;

answered Feb 16 '16 at 00:09

Inpego

2,657
13
14

score 0 · Answer 2 · answered Feb 16 '16 at 00:10

0

SELECT * FROM the_table t
WHERE EXISTS (
    SELECT * FROM the_table x
    WHERE x.amount = -1*t.amount
    -- AND x.amount > t.amount
    );

answered Feb 16 '16 at 00:10

wildplasser

43,142
8
66
109

score 0 · Answer 3 · answered Feb 16 '16 at 00:14

0

Consider storing an absolute value indexed column then query for the positive value. Postgres has an absolute value function; but I think the beauty of ActiveRecord is that Arel abstracts away the SQL. DB specific SQL can be a pain if you change later.

answered Feb 16 '16 at 00:14

Michael K Madison

2,242
3
21
35

score 0 · Answer 4 · answered Feb 16 '16 at 05:53

0

There is type called abs which will return irrespective of symobol. From my example data is the table name

SELECT id,amount  FROM DATA WHERE id = ABS(amount)

This is the sample test table

Here is the output

answered Feb 16 '16 at 05:53

Anoob K Bava

598
7
19

this doesnt even answer my question. – Deekor Feb 16 '16 at 16:25

find rows where there is another row with an opposite value in the table

4 Answers4