Table A has following fields and data
CID, Addkey, Cplan, changeDt
1,1a, planA, 10/10/2010
2, 1b, planD, 12/10/2010
3, 2a planE, 07/15/2010
4, 1a, planA, 06/22/2010
5, 1b, planD, 10/23/2010
6 1c, planA, 12/10/2010
7, 1a, planA, 06/06/2011
I want to find all CIDs where Addkey, Cplan are same and set them as master and child CID.
MasterCID, ChildCID. The CID with most recent changeDt should be the masterCID and the other one will be the childCID. ex
How do I do that using sql (MS Sql Server 2008)?