As an audit exercise, I need to find duplicate check numbers.
That is if I do find a check number that has been
used more than once for a specific bank I need to ignore
the first instance but capture the other (dups).
In the examle below there are 3 instances of check # 1
written on bank 'B'. What I'd like returned are PKs
3, 4 and 5 or more preferably only PKs 4 and 5 (3 is
the first one).
Can this be done in a SQL single statement?
CREATE CURSOR bnk_trx (pk I, bnk c(1), chkno C(1), amt y)
INSERT INTO bnk_trx VALUES (1, 'A', '1', 1)
INSERT INTO bnk_trx VALUES (2, 'A', '2', 2)
INSERT INTO bnk_trx VALUES (3, 'B', '1', 1)
INSERT INTO bnk_trx VALUES (4, 'B', '1', 2)
INSERT INTO bnk_trx VALUES (5, 'B', '1', 3)
SELECT * from bnk_trx WHERE EXISTS
(SELECT bnk, chkno, count(*) cnt FROM bnk_trx GROUP BY bnk, chkno ;
having count(*) > 1)