Anyone actually check this code?
I believe the RANK statement in the CTE is incorrect (see corrected version below) if the intent is to remove duplicates for ItemNumber only. At the very least, to return the dataset the author provides in the article:
Of course, I have to agree with others on this, for this particular example, GROUP with MIN would achieve the same result much more easily.
with GetMissingItems(Rank, RowNumber, ItemNumber, UnitCost) as (
--RANK() over (order by ItemNumber, UnitCost) as Rank
RANK() over (order by ItemNumber) as Rank
, ROW_NUMBER() over (order by ItemNumber, UnitCost) as RowNumber
where Rank = RowNumber
, min(UnitCost) 'UnitCost'
group by ItemNumber