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

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
PKTGAsked:
Who is Participating?
 
mfhorizonConnect With a Mentor Commented:
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
 
mfhorizonConnect With a Mentor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
sm394Connect With a Mentor Commented:
--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
 
mfhorizonConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.