I need to clarify :
elutin (6/2/2010)
This one, does return proper results, However, on the given dataset sample, it performs 3 scans & 26 logical reads (incl. 24 reads of CTE worktable) vs 34 scans but only 7 logical reads. ...
This should read as:
This one (submitted by Mark-101232), does return proper results, However, on the given dataset sample, it performs 3 scans & 26 logical reads (incl. 24 reads of CTE worktable) vs 4 scans but only 7 logical reads by "quirky update" method.
...
Also, Chris-Morris version of CTE just on 29 test data rows in the #Temp performs :
Table 'Worktable' (CTE). Scan count 5, logical reads 369
Table '#Temp'. Scan count 60, logical reads 60
All versions should be tested against the real data. Would be interesting to know which one performs best...