Solved

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

Posted on 2009-04-10
7
287 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

912 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