I don't get it. "SELECT 1" is significantly more informative than "SELECT red_herring". It's a self-documenting and automatically enforced comment telling you that the information requested by the SELECT statement is not used by the query.
I copy paste SQL all the time, especially with CTE after CTE all the time. The row number is a simple thing to copy paste that fills multiple functions with zero modification.
And that's different from 1 in what way other than being more confusing?
Note that in the case you're advocating for, the row number is called "OrderId", which you might have trouble with if you insist on copying a query from somewhere else and using it without modification. Wouldn't you prefer "1"?
It's helpful within CTEs as a unique identifier. This whole conversation makes me wonder if not many people minmax this sort of meta programming thing.
