Comment by sgarland

To clarify, I mean you can’t do this:

    SELECT id AS foo
    FROM MyTable
    WHERE foo = 1;

Similarly, you can’t do this:

    SELECT id
    FROM MyTable
    WHERE id = MAX(id);

Because in both cases, when the WHERE predicate is being executed, the engine doesn’t yet know what you’re asking it to find - for the former, SELECT hasn’t yet been called, so the alias isn’t applied; for the latter, the aggregation happens after filtering, so it can’t know what that maximum value is.

You can of course alias columns for SELECT however you’d like, and can also use those aliases in ORDER BY. You can also trivially refactor the above examples to use subqueries or CTEs to accomplish the goal.