I have a table in which I wish to return all rows where the items have duplicate information.
My table has 3 numeric columns - check number, account, amount
I would like to return all columns of all rows where the account and check number are equal, but with different amounts (indicating the second row may be a fraudulent item - ie copy of a check with amount altered).
Example MyTable data:
chknum account amount
------ ------- -------
123 123456 1.00
123 123456 2.00
234 234542 1.00
765 415834 7.00
239 901867 11.00
239 901867 22.00
240 901867 33.00
241 901867 44.00
Desired output:
chknum account amount
------ ------- -------
123 123456 1.00
123 123456 2.00
239 901867 11.00
239 901867 22.00
I have tried the following based on another post with some success to identify items, but it doesn't provide the desired detailed output:
SELECT * FROM (
SELECT t.chknum,t.account,t.amount, ROW_NUMBER() OVER (PARTITION BY account, chknum,amount ORDER BY account, chknum,amount) AS rn
FROM myTable t
)
WHERE rn > 1
Any help would be appreciated.