Jeff, thanks for your patience. I am learning a LOT on this thread.

Here is a variation on your code that will run faster...

Sergiy, I avoided using temporary tables for no reason. I looks like they might offer better performance.

CTE just places the block that runs first at the top of the code,...

Ouch... I obviously did not play enough with Jeff's solution before posting, my bad...

Jeff, your solution is outstanding.

Thanks

Antonio Macedo

Jeff,

My code took 11 minutes to process the 1000000 rows table.

That was on a single Pentium 4, 3Ghz, 1.5Gb RAM, single processor laptop.

Peter, nice use of the dense_rank function, I...

Using the MyHead table that Jeff provided, I came up with this solution.

I am using SQL 2005 so it might need a few changes to run in older versions.