Solved

# Get list of all duplicate transactions

Posted on 2011-10-18
219 Views
Good Day

Please assist in listing the duplcate transaction from the following table

I am having a table as follows

table Accounts
Columns
Accountno
LedgerEntry

Data is as follows
AccountNo            LedgerEntry
1                                    AA
1                                    AA
1                                    AA
2                                    BB
2                                    BB
3                                    CC
3                                    CC
3                                    CC
3                                    CC

Results Should be

AccountNo            LedgerEntry
1                                    AA
1                                    AA
2                                    BB
3                                    CC
3                                    CC
3                                    CC

I need to remove always One entry if duplicates are found

For example if there is a 3 * duplicate for AccountNo 1 then two records should stay, because I need to create a list of all duplicates on accountno 1
except the original transaction
0
Question by:henryreynolds

LVL 15

Expert Comment

for 2005 and above

CREATE TABLE #Account
(AccountNo INT,
LedgerEntry CHAR(2)
)

INSERT INTO #Account
SELECT 1, 'AA'
UNION ALL
SELECT 1, 'AA'
UNION ALL
SELECT 1, 'AA'
UNION ALL
SELECT 2, 'BB'
UNION ALL
SELECT 2, 'BB'
UNION ALL
SELECT 3, 'CC'
UNION ALL
SELECT 3, 'CC'
UNION ALL
SELECT 3, 'CC'
UNION ALL
SELECT 3, 'CC'

SELECT * FROM #Account

SELECT AccountNo, LedgerEntry
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY AccountNo, LedgerEntry ORDER BY AccountNo, LedgerEntry ) ID ,*
FROM #Account
)T
WHERE ID<> 1

DRO TABKE #Account
0

LVL 23

Expert Comment

``````;with cte as
(
select *, row_number() over( partition by AccountNo, LedgerEntry order by AccountNo, LedgerEntry) Serial
from table
)
-- select * from cte where Serial > 1
delete from cte  where Serial > 1
``````
0

Author Comment

I am running sql server 2000
0

LVL 22

Expert Comment

Try this
``````SELECT A.*
FROM
(SELECT DISTINCT Accountno, LedgerEntry FROM Accounts) A INNER JOIN
(SELECT Accountno, LedgerEntry, total=COUNT(1)-1
FROM Accounts
GROUP BY Accountno, LedgerEntry
) T ON A.Accountno=T.Accountno AND A.LedgerEntry=T.LedgerEntry INNER JOIN
master..spt_values n ON n.type='P' AND n.number BETWEEN 1 and total
``````
0

LVL 142

Expert Comment

for the note, you will find this article interesting in the context:
http://www.experts-exchange.com/A_3203.html
0

LVL 23

Accepted Solution

Since Sql Server 2000 is not supporting Row_Number() and CTE, you can try another logic.

Logic:-
1. Create a temporary table with the same table structure of table 'Accounts'
2. Insert to this temp table, DISTINCT data from 'Accounts' table
3. Delete all records from 'Accounts' table
4. Insert into 'Accounts' table from 'Accounts_temp' table
5. Drop temp table

Please see the below posted script
``````-- check before you start - there may be duplicates
select * from Accounts

-- Step 1:- create temporary table
create table Accounts_Temp
(
AccountNo    int,
LedgerEntry	char(2)
)
go

-- Step 2:- Select from here for execution *************
BEGIN TRAN T1

-- fetch the distinct data (removing duplicates) into a temporary table
insert into Accounts_Temp select distinct * from Accounts
-- delete all data from original table
delete from Accounts
-- insert into original table from temp table (having no duplicates)
insert into Accounts select * from Accounts_Temp

COMMIT TRAN T1
-- Select until here for execution *************
---------------------------

-- drop temporary table
drop table Accounts_Temp

-- check now - there may be no duplicates
select * from Accounts
``````

Raj
0

Author Closing Comment

thank you very much
0

## Featured Post

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.