Find duplicate value in the table via sql

Question

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.

Does this answer your question? [Finding duplicate values in a SQL table](https://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table) — Martijn, Jul 21 '22 at 08:17

score 0 · Answer 1 · answered Jul 20 '22 at 05:33

0

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;

answered Jul 20 '22 at 05:33

Courser Xu

142
4

Am I understanding the field correspondence correctly? – Courser Xu Jul 20 '22 at 05:35

score 0 · Answer 2 · edited Sep 10 '22 at 18:42

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 query 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.

Find duplicate value in the table via sql

2 Answers2