title: Choosing best scored result using tsql
body: <p>I have a set of data that I need to choose the best match.  Each record has a Name and CNum.  Each record with the same 'Name' should have the same 'CNum'; in reality some 'Name' matches have the same CNum and some do not (this is the issue to resolve).  I need to determine which CNum is better and update all 'Name' matches with a single CNum.</p>
  
  <p>I've updated the table with a ParentId column to show matching Names, and a SubParentId to mark matching 'Name' and 'CNum' to help extract the desired results (and make it easier to see matches).</p>
  
  <p>To help determine which CNum is better than another within a 'Name' group, each record has been scored in two columns: 'ScoreA' and 'ScoreB'; the lower the score the better.  Here are the rules I'm using to determine which CNum is best:</p>
  
  <ol>
  <li>if all records in the name group (same ParentId) have the same CNum (same SubParentId), do nothing</li>
  <li>if they don't all have the same CNum within the same Name group,  select the id of the record with the single lowest ScoreA and update the group's parentId to the selected id</li>
  <li>if there isn't a single ScoreA match, update each of the group's parentId with the id of the record with the single lowest ScoreB</li>
  <li>if there isn't a single ScoreB match, and it is a tie between different Regions and only one of them has an 'AB' Region,  update each of the group's parentId with the id of the record with a Region of 'AB'</li>
  <li>if still not match or multiple 'AB' regions (with different CNum and tied scores), set NoBestMatch=1 for each record within that 'Name' group</li>
  </ol>
  
  <p>ASSUMPTION: if they have the same Name and CNum their scores will be the same</p>
  
  <p>Is there a good way to apply the above rules to get the results I'm looking for?</p>
  
  <p>Here's a sample of the data and the results I'm looking for with the expected winning results noted next to the insert statements:</p>
  
  <pre><code>-- create table
  CREATE TABLE Results
  (
      Id          INT NOT NULL IDENTITY( 1, 1 ) PRIMARY KEY,
      Name        VARCHAR(200) NULL,
      CNum        NVARCHAR(100) NULL,
      Region      NVARCHAR(3) NULL,
      ScoreA      INT NULL,
      ScoreB      INT NULL,
      ParentId    INT NULL,
      SubParentId INT NULL,
      NoMatch     BIT NOT NULL DEFAULT(0)
  )
  GO
  
  -- insert data
  
  -- Leave as is: they are all the same
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Grasslands', '91588', 'WY', '-668', '13' )
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB )
      VALUES ( 'Grasslands', '91588', 'WY', '-668', '13' )
  
  --  Acme Co: winner noted below --&gt; best ScoreA
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Acme Co', '269415003', 'AB', '-13455', '-23' )
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Acme Co', '269415003', 'AB', '-13455', '-23' )
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Acme Co', '5695003', 'AB', '-155', '-23' )
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Acme Co', '269415003', 'AB', '-13460', '-23' ) -- Expected Winner
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Acme Co', '5695003', 'AB', '-155', '-23' )
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Acme Co', '5695003', 'AB', '-155', '-23' )
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Acme Co', '856545', 'AB', '-22', '16' )
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Acme Co', '856545', 'AB', '-22', '16' )
  
  --  Zuland Ltd: winner noted below --&gt; best ScoreB
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Zuland Ltd', '654543', 'AB', '-13455', '-28' )    
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Zuland Ltd', '654543', 'AB', '-13455', '-28' )
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Zuland Ltd', '654543', 'AB', '-13455', '-23' )
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Zuland Ltd', '5603', 'ON', '-13455', '-30' )  -- Expected Winner
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Zuland Ltd', '5603', 'ON', '-13455', '-23' )
  
  --  Emco Inc: winner noted below --&gt; AB tie breaker
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Emco Inc', '5695003', 'ON', '-668', '13' )    
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Emco Inc', '5695003', 'AB', '-668', '13' ) -- Expected Winner
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Emco Inc', '5545', 'CA', '-668', '13' )
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Emco Inc', '5545', 'CA', '-668', '13' )
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Emco Inc', '995588', 'WY', '-668', '13' )
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Emco Inc', '995588', 'WY', '-668', '13' )
  
  -- Zemco Inc: No Winner --&gt; No AB tie breaker
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Zemco Inc', '5695003', 'TN', '-668', '13' )   
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB )
      VALUES ( 'Zemco Inc', '5695003', 'TN', '-668', '13' )
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Zemco Inc', '5545', 'CA', '-668', '13' )
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB )
      VALUES ( 'Zemco Inc', '995588', 'WY', '-668', '13' )
  
  -- Texco Inc: No Winner --&gt; No AB tie breaker
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Texco Inc', '234JJJ', 'TN', '-668', '13' )
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Texco Inc', '555552', 'TN', '-668', '13' )
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Texco Inc', '234JJJ', 'CA', '-668', '13' )
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Texco Inc', '555552', 'WY', '-668', '13' )
  
  -- Grasslands: Leave as is --&gt; they are all the same
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Grasslands', '91588', 'WY', '-668', '13' )    
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB )
      VALUES ( 'Grasslands', '91588', 'WY', '-668', '13' )
  
  -- Mike Inc: No Match --&gt; more than 1 'AB' with tied scores
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Mike Inc', '234JJJ', 'AB', '-668', '13' )
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Mike Inc', '555552', 'AB', '-668', '13' )
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Mike Inc', '234JJJ', 'AB', '-668', '13' )
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB )
      VALUES ( 'Mike Inc', '555552222', 'WY', '-668', '13' )
  INSERT INTO Results ( Name, CNum, Region, ScoreA, ScoreB ) 
      VALUES ( 'Mike Inc', '90210', 'KT', '-668', '13' )
  
  GO
  
  -- set parent id matched on Name
  UPDATE  r
  SET     r.ParentId = COALESCE( r1.Id, r.Id )
  FROM    Results r
  LEFT JOIN Results r1
    ON    r.Name = r1.NAME
  GO
  
  -- set sub-parent id matched on Name and CNum
  UPDATE  r
  SET     r.SubParentId = COALESCE( r1.Id, r.Id )
  FROM    Results r
  LEFT JOIN Results r1
    ON    r.Name = r1.Name AND
          r.CNum = r1.CNum
  GO
  </code></pre>