That's valid DuckDB syntax

DuckDB has some really nice syntax updates that I hope get added to the ISO spec, like GROUP BY ALL and GROUP BY aliases.

I like their approach of adding thoughtful quality of life improvements instead of coming up with a new language.

https://duckdb.org/2022/05/04/friendlier-sql.html

Most of the improvements in the article, including unrestricted aliases, were added in ClickHouse and subsequently influenced DuckDB. They still have to implement many usability and language improvements from ClickHouse.

Didn't know that, thanks.

> GROUP BY aliases

We're migrating from Sybase SQLAnywhere to MSSQL, and not being able to use aliases in WHERE, GROUP BY and ORDER BY is such a pain in the behind.

Almost all our non-trivial queries have to be nested multiple levels due to this, which doesn't exactly help readability.

Ugh, I'll be having to do this same migration soon for one of my clients. Not looking forward to it. Good to know!

I would love if base SQL were improved. On the other hand, we are just slapping lipstick on a pig. It is already a gargantuan mess which needs a path to replacement.

Then again, Oracle just this year added support for booleans, so asking the incumbents to switch to a new query language seems an impossible ask.

Agreed... Their SQL is awesome and seemingly only getting better.

My favorite is window aliases (I'm sure it's found in other SQL engines too). It's not only cleaner and less likely to accidentally introduce a discrepancy, but apparently also results in better performance.

> The three window functions will also share the data layout, which will improve performance.

https://duckdb.org/docs/sql/window_functions#window-clauses

I'm surprised some of the other big-name OLAPs don't provide this. *cough* Snowflake *cough*. Snowflake's query optimizer doesn't even seem to recognize common window definitions across multiple window functions.

Because the DuckDB folks used the Postgres query parser.

It's been an alternative in Postgres for decades.

In Postgres, it's

   TABLE foo
   LIMIT 100;

No SELECT with columns and no FROM keyword.