By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
--drop table a;
create table a (
a1 varchar,
a2 varchar
);
insert into a values ( 'a', 1 );
insert into a values ( 'a', 3 );
insert into a values ( 'a', 5 );
insert into a values ( 'a', 9 );
insert into a values ( 'b', 1 );
insert into a values ( 'b', 3 );
insert into a values ( 'b', 4 );
insert into a values ( 'c', 3 );
insert into a values ( 'c', 4 );
insert into a values ( 'c', 5 );
-- This works fine.
select a1
, a2
, lag(a2)
over (partition by a1 order by a2)
from a
;
-- This fails.
select a1
, a2
, lag(a2)
over (partition by a1 order by a2) as new_col_name
from a
;
-- But if I can't name the column, I can't CTAS.
| a1 | a2 | (No column name) |
|---|---|---|
| a | 1 | null |
| a | 3 | 1 |
| a | 5 | 3 |
| a | 9 | 5 |
| b | 1 | null |
| b | 3 | 1 |
| b | 4 | 3 |
| c | 3 | null |
| c | 4 | 3 |
| c | 5 | 4 |
| a1 | a2 | new_col_name |
|---|---|---|
| a | 1 | null |
| a | 3 | 1 |
| a | 5 | 3 |
| a | 9 | 5 |
| b | 1 | null |
| b | 3 | 1 |
| b | 4 | 3 |
| c | 3 | null |
| c | 4 | 3 |
| c | 5 | 4 |
sELECT * FROM b
| a1 | a2 | new_column |
|---|---|---|
| a | 1 | null |
| a | 3 | 1 |
| a | 5 | 3 |
| a | 9 | 5 |
| b | 1 | null |
| b | 3 | 1 |
| b | 4 | 3 |
| c | 3 | null |
| c | 4 | 3 |
| c | 5 | 4 |