'ORDER BY 2' would work here, but using the named column is a lot nicer.

Wow, TIL. Great tip for those random one-off queries you have to bash out when investigating a problem.

Please never let this vile shortcut work its way into your production code.

I've seen quite a few production queries that use indexes in GROUP BY and ORDER BY; it's quite common. Probably partially because linters/code review/etc are lightweight to nonexistent amongst the analysts/data science types that I tend to work with.

Indexes are used all over for grouping an ordering, I was objecting only to the syntax of ORDER BY <number>

SQL already supports "order by complicated_count". Did you mean group by?

This isn't really the large, convincing example I was looking for btw.

Many dialects already support using aliases in GROUP BY and HAVING too, btw.

IMO it's most useful (though somewhat more difficult to implement) to be able to use the aliases with window functions or large case/when statements, something like

   SELECT
     page,
     SUM(clicks) AS total_clicks,
     100. * total_clicks / (SUM(total_clicks) OVER ()) AS click_pct,
     100. * SUM(total_clicks) OVER (ORDER BY total_clicks DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / (SUM(total_clicks) OVER ()) AS cumulative_click_pct
    FROM weblog
    GROUP BY page;

Interesting, ta. My code rarely looks like that so thanks for the insight. Was exactrly what I was looking for.