CODEWITHSUNDEEP
sqlsql-servert-sqlstring-matchingfuzzy-search
Dede Muhamad Yusup
Dede Muhamad Yusup

Reputation: 9

SELECT SQL Matching Number

I have millions of rows of data that have similar values ​​like this:

Id    Reff    Amount
1     a1       1000
2     a2      -1000
3     a3      -2500
4     a4      -1500
5     a5       1500

every data must have positive and negative values. the question is, how do I show only records that don't have a similar value? like a row Id 3. thanks for help

Upvotes: 0

Views: 1984

Answers (3)

LukStorms
LukStorms

Reputation: 29647

Using a NOT EXISTS or a LEFT JOIN will work fine to find the amounts that don't have an opposite amount in the data.

But to really find the amounts that don't balance out with an Amount sorted by ID?
For such SQL puzzle it should be handled as a Gaps-And-Islands problem.

So the solution might appear a bit more complicated, but it's actually quite simple.

It first calculates a ranking per absolute value.

And based on that ranking it filters the last amount where the SUM per ranking isn't balanced out (not 0)

SELECT Id, Reff, Amount
FROM
(
    SELECT *,
     SUM(Amount) OVER (PARTITION BY Rnk) AS SumAmountByRank,
     ROW_NUMBER() OVER (PARTITION BY Rnk  ORDER BY Id DESC) AS Rn
    FROM
    (
        SELECT Id, Reff, Amount,
         ROW_NUMBER() OVER (ORDER BY Id) -  ROW_NUMBER() OVER (PARTITION BY ABS(Amount) ORDER BY Id) AS Rnk
        FROM YourTable
    ) AS q1
) AS q2
WHERE SumAmountByRank != 0
  AND Rn = 1
ORDER BY Id;

A test on rextester here

If the sequence doesn't matter, and just the balance matters?
Then the query can be simplified. 

SELECT Id, Reff, Amount
FROM
(
    SELECT Id, Reff, Amount,
     SUM(Amount) OVER (PARTITION BY ABS(Amount)) AS SumByAbsAmount,
     ROW_NUMBER() OVER (PARTITION BY ABS(Amount) ORDER BY Id DESC) AS Rn
    FROM YourTable
) AS q
WHERE SumByAbsAmount != 0
  AND Rn = 1
ORDER BY Id;

Upvotes: 0

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE Test(
   Id     int  
  ,Reff   varchar(2)
  ,Amount int  
);
INSERT INTO Test(Id,Reff,Amount) VALUES (1,'a1',1000);
INSERT INTO Test(Id,Reff,Amount) VALUES (2,'a2',-1000);
INSERT INTO Test(Id,Reff,Amount) VALUES (3,'a3',-2500);
INSERT INTO Test(Id,Reff,Amount) VALUES (4,'a4',-1500);
INSERT INTO Test(Id,Reff,Amount) VALUES (5,'a5',1500);

Query 1:

select t.*
from Test t
left join Test t1 on t1.amount =ABS(t.amount)
where t1.id is null

Results:

| Id | Reff | Amount |
|----|------|--------|
|  3 |   a3 |  -2500 |

Upvotes: 2

GMB
GMB

Reputation: 222462

You can use not exists:

select t.*
from mytable t
where not exists (select 1 from mytable t1 where t1.amount = -1 * t.amount)

A left join antipattern would also get the job done:

select t.*
from mytable t
left join mytable t1 on t1.amount = -1 * t.amount
where t1.id is null

Demo on DB Fiddle:

Id | Reff | Amount
-: | :--- | -----:
 3 | a3   |  -2500

Upvotes: 3

Related Questions