Today I Learned

A Hashrocket project

Group by and order by can use aliases

In PostgreSQL the alias of a selected expression can be used in the group by and order by clauses instead of repeating the expression.

For example, this:

select left(lower(email), 1) as first_letter, count(*) as num
from users
group by left(lower(email), 1)
order by count(*) desc;

Can be replaced with this:

select left(lower(email), 1) as first_letter, count(*) as num
from users
group by first_letter
order by num desc;
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, a series of free weekly PostgreSQL screencasts.