Since this is the 2008 forum, I would guess you would be okay running this:EXEC sp_dbcmptlevel 'DatabaseName', 100

... but you should run that by any DBAs you may have available.

I don't know if this is really what you're looking for, but I took a stab for fun.

First, I loaded up some test data...

CREATE TABLE #Orders

(

OrderID INT IDENTITY(1,1),

...

Sure...

dwain.c (6/1/2012)

---

jeffem (6/1/2012)

---

Dwain, good job on accounting for the missing IDs! 🙂

Just a note about the performance: I did a comparison, and using a table variable will give a bit...

Jeff-

This really confused me for a while, until I made an assumption that you meant "can't". Assumption correct? If not, could you help me understand how you mean it?

It doesn't hurt to do it with a left join, but if you use an inner join there, you don't need to require where t2.id is not null.

Dwain, good job on accounting for the missing IDs! 🙂

Just a note about the performance: I did a comparison, and using a table variable will give a bit of a...

A good point! It seems to me the best way to handle it might be to create a ROW_NUMBER(), to have perfectly sequential IDs, then use the solutions presented before....

If you want something simpler than ColdCoffee's solution, but not nearly as efficient (according to the estimated execution plan, unless you index the join and include the return values), using...

Señor Magoo,

That is some terrific info and very easy to digest! I greatly appreciate the time you spent on that. It helps me understand CTE's a bit better, and now...

Really, both are important, in a sense. I already HAVE the results in the form I wanted (and not by just deleting results rows); I ended up creating a temp...

OK, I just figured out to add

WHERE RowNum = 1 in the anchor portion of the recursive CTE, but I'm still not quite there.