Solved

How to delete duplicate records based on specific columns in SQL Serevr 2005 / 2008?

Posted on 2009-04-10
7
285 Views
Last Modified: 2012-05-06
I have the table A and it doesn't have any PK or unique key . I want to remove duplicate rows based on DeptID and Date. And I need to keep min(sessionid) for the duplicate records in the table and remove other one.
SessionID  DeptID   Date          accessdate  status
500             100     20090410   20010101    2
561             100      20090410  20010105    5
590              100     20090410  20021212    5
600              101     20090409  20050507    3
610              101    20090409   20050510    4
700              103     20090410  20071012   3
701               105   20090410   20081011   3

ForDeptID 100, i need to keep  500             100     20090410   20010101    2 row and delete remaining Deptid 100 rows.  Please advise me how to do this efficiently.  Tahnks in advance
0
Comment
Question by:PKTG
7 Comments
 
LVL 5

Assisted Solution

by:mfhorizon
mfhorizon earned 150 total points
ID: 24116637
User this query

select  DeptID, min(SessionID)SessionID into filteredTable from A
group by DeptID
go

Delete from A where convert(DeptID,varchar(10))+convert(SessionID,varchar(10)) not in (select convert(DeptID,varchar(10))+convert(SessionID,varchar(10)) from filteredTable)
go

select * from A

0
 
LVL 60

Accepted Solution

by:
chapmandew earned 100 total points
ID: 24116640
0
 
LVL 5

Assisted Solution

by:mfhorizon
mfhorizon earned 150 total points
ID: 24116666
I have assumed that DeptID and SessionID are integer in the table...

Let me read out above queries:

1. select all deprtment id's and corresponding minimum session id's in the table A and store them to another table filteredTable

2. delete from table A all those where combined string DeptID&SessionID is not found in the list of DeptID&SessionID fromt the filteredTable

Please take backup of your table(s) before proceeding with this logic. You may need to restore the backup and do some tweaking according to the case.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 11

Assisted Solution

by:bmatumbura
bmatumbura earned 50 total points
ID: 24116693
0
 
LVL 13

Assisted Solution

by:sm394
sm394 earned 100 total points
ID: 24116723
--verify
select *
from(
      select row_number() over(partition by DeptID  , Date order by SessionID  ) as RowNo,*  
      from YOURTABLE
      ) t1
where RowNo>1

--Delete
delete t1
from(
      select row_number() over(partition by DeptID  , Date order by SessionID  ) as RowNo,*  
      from YOURTABLE
      ) t1
where RowNo>1
0
 
LVL 5

Assisted Solution

by:mfhorizon
mfhorizon earned 150 total points
ID: 24116725
In SQL SERVER 2005 and SQL SERVER 2008 there is CAST functionwhich is more reliable so I will repeat my query

select  DeptID, min(SessionID)SessionID into filteredTable from A
group by DeptID
go

Delete from A where Cast(DeptID as varchar(10))+cast(SessionID as varchar(10)) not in (select Cast(DeptID as varchar(10))+cast(SessionID as varchar(10)) from filteredTable)
go

select * from A

ENJOY!!!
0
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 100 total points
ID: 24117026
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

705 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now