Based on your sample data, this would work, but it relies on #DEF.anotherid being unique and inserted into the table in numerical order - it's not a particularly nice piece of T-SQL!

SELECT

'#ABC',

A.*,

'#DEF',

MIN(b.id) id,

MIN(b.anotherid) anotherid FROM #ABC A

LEFT OUTER JOIN #DEF B ON A.anotherid <= B.[anotherid]

GROUP BY a.id, a.[anotherid]

If you didn't need to see #DEF.id in your results then you could drop the column from your select list and this code should work regardless of the order #DEF.anotherid is inserted in.

Just out of curiosity, what is it you are trying to do?

Cheers,

Simon 🙂