Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-04-10
7
Medium Priority
?
292 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 5

Assisted Solution

by:mfhorizon
mfhorizon earned 600 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 400 total points
ID: 24116640
0
 
LVL 5

Assisted Solution

by:mfhorizon
mfhorizon earned 600 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 11

Assisted Solution

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

Assisted Solution

by:sm394
sm394 earned 400 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 600 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 400 total points
ID: 24117026
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

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