4 ms·
for example select id, count(...something complicated) as complicated_count from .... order by complicated_count would help
by yread 4y ago
for example
select id, count(...something complicated) as complicated_count
from ....
order by complicated_count
would help
- jsmith99 4y ago'ORDER BY 2' would work here, but using the named column is a lot nicer.
- tomjakubowski 4y agoWow, TIL. Great tip for those random one-off queries you have to bash out when investigating a problem.
- zasdffaa 4y agoPlease never let this vile shortcut work its way into your production code.
- sagarm 4y agoI'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.
- zasdffaa 4y agoIndexes are used all over for grouping an ordering, I was objecting only to the syntax of ORDER BY <number>
- zasdffaa 4y agoSQL already supports "order by complicated_count". Did you mean group by? This isn't really the large, convincing example I was looking for btw.
- sagarm 4y agoMany 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;
- zasdffaa 4y agoInteresting, ta. My code rarely looks like that so thanks for the insight. Was exactrly what I was looking for.