Interesting question. Works in current versions but shows bad practise.

Every SQL Server version I have known would do this, but in theory the value returned could be any row of the table - the optimiser could detect that no row-set is returned by the first select so as soon as the engine reads one value it can use that for the assignment. Even if a rowset were returned, the the rowset could be sorted as the last action of the select statement, ie after the assignment, so not assigning the value from last row according to the ORDER BY clause.

So (at least in theory) it's bad practice to rely on getting 1 here. A clever optimser would make the first select assign 2 (the first row and only selected) and discard the ORDER BY clause. That's what people keep on telling me might happen, and I regard it as a failure of the language definition that meaningless ORDER BY clauses are note precluded by the syntax definition, and I haven't seen any argument that an order clause like this one is meaningful.

Personally I think that for simple cases like this one it would be far more sensible (and deliver better perormance) to write "SELECT @x = MIN(Value) FROM #values" instead of something with a meaningless and potentially ineffective order by clause.