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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.