Janning Vygen <vygen@gmx.de> writes:
> I have a guess, what happens here: The order of the subselect statement is
> dropped by the optimizer because the optimizer doesn't see the "side-effect"
> of the ranking function.
That guess is wrong.
I think the problem is that you are trying to update multiple rows in
the same statement, which would require a "reset ranking" between each
row, which this approach doesn't provide for.
The whole thing looks mighty fragile in other ways; anything involving a
single global variable isn't going to work nicely in very many cases.
Consider casting your solution as an aggregate instead...
regards, tom lane