2

In the code below, I can do the LAG and PARTITION BY with no problem as a query. But I need it as a table. If I do CTAS, I need an alias on the column. But when I try to put an alias on it, it fails. I think this is valid SQL. But I'm using Snowflake, so I'm asking it on Super User as I fear this is a vendor issue.

How can I create a table with the output of that query?

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) new_col_name
  over (partition by a1 order by a2)
  from a
;

-- But if I can't name the column, I can't CTAS.
create table b as
select a1
     , a2
     , lag(a2)
  over (partition by a1 order by a2)
  from a
;

Output is:

status
A successfully dropped.
status
Table A successfully created.
number of rows inserted
                      1
number of rows inserted
                      1
number of rows inserted
                      1
number of rows inserted
                      1
number of rows inserted
                      1
number of rows inserted
                      1
number of rows inserted
                      1
number of rows inserted
                      1
number of rows inserted
                      1
number of rows inserted
                      1
A1  A2  LiteralRedacted0
b   1   NULL
b   3   1
b   4   3
c   3   NULL
c   4   3
c   5   4
a   1   NULL
a   3   1
a   5   3
a   9   5
001003 (42000): SQL compilation error:
syntax error line 4 at position 2 unexpected 'over'.
002022 (42601): SQL compilation error:
Missing column specification

2 Answers 2

2

lag(...) over(...) is an entire expression, you cannot split it at will. The correct syntax is

select a1
     , a2
     , lag(a2) over (partition by a1 order by a2) new_col_name
from a
2

SQL server has another syntax for creating tables from another select.

Also you need to do that all columns named

--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.

select a1
     , a2
     , lag(a2)
  over (partition by a1 order by a2) as new_column

  INTO b
  from a
;
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

fiddle

1
  • Oh, flippin' brilliant! The "lag" and "over" go together. Duh. Thank you!!! I was thinking the lag function stood alone similar to aggregate functions like "min" or "max", so I put the alias right after the function. But the whole "lag" clause goes to the parenthesis after internal order by clause. Not just a SQL Server thing. It works on Snowflake. Thanks! Feb 22 at 18:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.