Hmm… my hunch is that this doesn’t do what you think it does.
I expect the order by in the window function is effectively lost because max operated over the whole window. (And you happen to get the most recent, because in many implementations, order_id will be a sequence.)
But I might be wrong. And I might only now be learning that order by with max() and over substitutes how the “value” of the order_id is understood.
Here's a pretty simple/normalish way to handle the edge cases. This one (without distinct) is far more consistent (wall-clock-wise, doesn't depend on caches): http://sqlfiddle.com/#!15/7eb3a/9
Note that order 2 is after order 4 in the example schema.
I expect the order by in the window function is effectively lost because max operated over the whole window. (And you happen to get the most recent, because in many implementations, order_id will be a sequence.)
But I might be wrong. And I might only now be learning that order by with max() and over substitutes how the “value” of the order_id is understood.