Find duplicate value in the table via sql-CodePudding

I have a table called stocktransfer Rows truncated to keep it simple here. as per the image the problem with the record is that there is duplicated transaction number across two different invoice number which is incorrect to context of how business logic is. So duplicate transaction is expected as long as it is under the same invoice number. I wrote this query but it does not help since the duplication is expected.

Select strefno,sttran,STDATE,count(sttran) 
From postfrh  
Group By sttran,strefno,STDATE 
Having Count(sttran) >1 
Order By sttran

Can anyone please help with how to write a logic to find duplicated transaction where invoice numbers are different two.

CodePudding user response：

strefno > TransctionNumber

sttran > InvoiceNumber

STDATE > date


    SELECT strefno,
             sttran,
             STDATE,
             row_number ( )
        OVER ( PARTITION BY strefno
    ORDER BY  STDATE ) AS `rowNumber`
    FROM postfrh
    WHERE strefno IN 
        (SELECT strefno
        FROM postfrh
        GROUP BY  strefno
        HAVING count( sttran ) > 1 )
    ORDER BY  strefno;

CodePudding user response：

you are probably looking for something like this. I don't have the exact table so I cannot be sure.

select a.tnum from postfrh as a, postfrh as b where a.tnum = b.tnum and b.inum!=a.inum

(tnum = transaction number, inum = invoice number)

there are several ways to approach the problem but the above quarry works by joining two instances of the table, the first condition in the where clause means that there will only be items with the same transaction number, the second statement filters out transactions that have the same transaction number and invoice number.