Erik Darling has a series on getting the highest value using CROSS APPLY. Part 1 covers the no-index route:
Let’s say you wanna get the highest thing. That’s easy enough as a concept.
Now let’s say you need to get the highest thing per user. That’s also easy enough to visualize.
There are a bunch of different ways to choose from to write it.
Part 2 covers the yes-index route:
In this round, row number had a tougher time than other ways to express the logic.
It just goes to show you, not every query is created equal in the eyes of the optimizer.
I don’t think I’m spoiling too much by saying that you really want a good index in place when using CROSS APPLY in this manner.