Return to Answer

So, in the simple example:

select 
    b, count_b            -- valid column aliases
    a, count_a            -- invalid here (have been overridden)
from 
    ( select t.a, count(*) as count_a
      from t
      group by t.a
      order by count_a desc           -- count_a is valid here
      limit 8
    ) 
      as d (b, count_b) ;

So, the simple example will give an error:

select 
    a, count_a              -- invalid here (have been overridden)
from 
    ( select t.a, count(*) as count_a
      from t
      group by t.a
      order by count_a desc           -- count_a is valid here
      limit 8
    ) 
      as d (b, count_b) ;

but this will work:

select 
    b, count_b              -- valid column aliases
from     
    -- identical as above 

Overall, the usefulness of the feature is at least debatable. And the above query that overrides the names of just 3 of the possibly many columns reeks obfuscation and could be very well considered bad practice.

Overall, the usefulness of the feature is at least debatable when used for base tables. And the above query that overrides the names of just 3 of the possibly many columns reeks obfuscation and could be very well considered bad practice.

But the feature is provided because it's standard SQL and for completeness. It wouldn't make sense to have this only for subqueries and CTEs and not for other kinds of tables.

I don't know of any syntax to allow you to alias only the 3rd column, without providing the names of the 1st and 2nd column.

So, in the simple example:

select 
    b, count_b            -- valid column aliases
    a, count_a            -- invalid here (have been overridden)
from 
    ( select t.a, count(*) as count_a
      from t
      group by t.a
      order by count_a desc           -- count_a is valid here
      limit 8
    ) 
      as d (b, count_b) ;

the names a and count_a are valid inside the subquery (derived table) but not outside because they have been overridden by b and count_b.

I don't know of any syntax to allow you to alias only the 3rd column, without providing the names of the 1st and 2nd column.

Overall, the usefulness of the feature is at least debatable. And the above query that overrides the names of just 3 of the possibly many columns reeks obfuscation and could be very well considered bad practice.

One case where it can be useful is (not with base table but with) the VALUES construct, where the columns get default names of column1, column2, etc. and this aliasing can be used to select more meaningful names:

select 
    a, b
from 
    ( values
         (1, 2),
         (2, 3), 
         (3, 5)
    ) 
      as d (a, b) ;