16 Feb 2017   sql


Postgres allow group by and order by to reference column order in a sql statement. This is particularly useful when an aggregate result needs to be referenced in the group by or order by statement.

-- group by aggregate
select
  (created_at at time zone 'UTC' at time zone 'America/Chicago')::date,
  count(*)
from posts
group by (created_at at time zone 'UTC' at time zone 'America/Chicago')::date
order by (created_at at time zone 'UTC' at time zone 'America/Chicago')::date
;

becomes

-- group by 1, 2, 3
select
  (created_at at time zone 'UTC' at time zone 'America/Chicago')::date,
  count(*)
from posts
group by 1
order by 1
;
🍄