[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Get list of all duplicate transactions

Posted on 2011-10-18
7
Medium Priority
?
223 Views
Last Modified: 2012-05-12
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
Comment
Question by:henryreynolds
7 Comments
 
LVL 15

Expert Comment

by:Anuj
ID: 36990948
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

by:Rajkumar Gs
ID: 36990973
;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

Open in new window

0
 

Author Comment

by:henryreynolds
ID: 36991077
I am running sql server 2000
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 22

Expert Comment

by:Thomasian
ID: 36991116
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

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36991117
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

by:
Rajkumar Gs earned 2000 total points
ID: 36991546
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

Open in new window


Alert - Take backup of your database before try this

Raj
0
 

Author Closing Comment

by:henryreynolds
ID: 37005350
thank you very much
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question