I would ask them if they are in the habit of routinely limiting developers to not using all the facets of a tool. 😉

Obviously what they want to understand is your knowledge of sql. The question is a very poor one imho. If you ask an interview question and ask someone to write code you shouldn't dictate they can't use powerful features of the language.

This reminds me of a software developers test I took. They asked me to write a linked list in .NET, the instructions also explicitly stated NOT to use the native LinkedList object. I wrote the code example and about a half of diatribe about how ridiculous it is to ask to disallow using features of a language. Obviously, the reason the object was written into the framework is because it is a complete PITA to roll your own. Using windowed functions is much the same.

Venting aside, you can do this without using "Row_Numbers, Cursors , functions etc". I did use an outer apply but this goes back to my previous comment.

Much thanks to Steve Willis for putting together sample data and ddl. 😀

delete #TempTable

where ID not in

(

select x.ID

from #TempTable t

outer apply

(

select top 1 tt.*

from #TempTable tt

where tt.Event = t.Event

and tt.ID <> t.ID

order by tt.EventDate desc

) x

where x.ID is not null

group by x.ID

)

select * from #TempTable order by Event, EventDate desc