Lateral Column Alias

Lateral column alias allows you to alias columns within a query and then use that same alias within the scope it was created.

select 1 as a,
  (a + 1) as b

Basically every other flavor of SQL, this is not possible. PostgreSQL 9.3+ supports this using the LATERAL keyword.

I’m generally very critical of Redshift and its ability to overcomplicate very simple things. However, I do like this feature quite alot.