undefined | Better HN

0 pointsSPBS5y ago0 comments

Yeah it's mentioned in this blog post https://blog.jooq.org/2014/01/06/the-sql-languages-most-miss... that SQL is missing Common Column Expressions, and that using Common Table Expressions reuse columns is simply a band-aid fix over the lack of those.

For example, your example would have been more naturally expressed as

    SELECT partcol1
    FROM (tbl WITH partcol1 AS AVG(col1) OVER (PARTITION BY col2))
    WHERE partcol1 > 10.0

0 comments

wenc5y ago

Wow thanks. I've never heard it expressed as a Common Column Expression. It makes sense to include it in the FROM statement because that's usually parsed and executed first.

j / k navigate · click thread line to collapse

0 pointsSPBS5y ago0 comments

For example, your example would have been more naturally expressed as

    SELECT partcol1
    FROM (tbl WITH partcol1 AS AVG(col1) OVER (PARTITION BY col2))
    WHERE partcol1 > 10.0

0 comments

wenc5y ago

Wow thanks. I've never heard it expressed as a Common Column Expression. It makes sense to include it in the FROM statement because that's usually parsed and executed first.

j / k navigate · click thread line to collapse