Waiting for SQL:202y: Group by All

elygre · 4 months ago

Let me reference fields as I create them:

  select xxxxx as a
       , a * 2 as b

zX41ZdbW · 4 months ago

This will be great! One of the things ClickHouse has had since 2016.

Deleted Comment

cyberax · 4 months ago

SQL needs to have `select` as the _last_ part, not the first. LINQ has had this for 2 decades by now: "from table_a as a, table_b as b where ... select a.blah, b.duh".

cryptonector · 4 months ago

This is not relevant to GP's point. This is a separate topic, which... I don't really care, but I know a lot of people want to be able to write SQL as you suggest, and it's not hard to implement, so, sure.

Though, I think it might have to be table sources, then `SELECT`, then `WHERE`, then ... because you might want to refer to output columns in the `WHERE` clause.

viraptor · 4 months ago

https://prql-lang.org/ and compile to SQL.

agnosticmantis · 4 months ago

The Pipe Query Syntax in GoogleSQL implements this elegantly as well:

https://docs.cloud.google.com/bigquery/docs/reference/standa...

jiggawatts · 4 months ago

Also in the Kusto Query Language (KQL) as used by Azure Log Analytics.

Exuma · 4 months ago

Also just let me reference the damn alias in a group by, FUCK

sbuttgereit · 4 months ago

At least in PostgreSQL, both by alias and ordinal are possible:

  localhost(from SCB-MUSE-BOXX).postgres.scb.5432 [Sun Nov 16 12:02:15 PST 2025]
  > create table test (a_key integer primary key, a_group integer, a_val numeric);
  CREATE TABLE
  Time: 3.102 ms

  localhost(from SCB-MUSE-BOXX).postgres.scb.5432 [Sun Nov 16 12:02:25 PST 2025]
  > insert into test (a_key, a_group, a_val) values (1, 1, 5.5), (2, 1, 2.6), (3, 2, 1.1), (4, 2, 6.5);
  INSERT 0 4
  Time: 2.302 ms

  localhost(from SCB-MUSE-BOXX).postgres.scb.5432 [Sun Nov 16 12:02:58 PST 2025]
  > select a_group AS my_group, sum(a_val) from test group by my_group;
   my_group | sum
  ----------+-----
          2 | 7.6
          1 | 8.1
  (2 rows)
  
  Time: 4.124 ms
  localhost(from SCB-MUSE-BOXX).postgres.scb.5432 [Sun Nov 16 12:03:15 PST 2025]
  > select a_group AS my_group, sum(a_val) from test group by 1;
   my_group | sum
  ----------+-----
          2 | 7.6
          1 | 8.1
  (2 rows)
  
  Time: 0.360 ms

mberning · 4 months ago

Some do. It would also be nice to reference by ordinal number similar to order by. Very handy for quick and dirty queries. I can see the issue though that people start to lean on it too much.

petereisentraut · 4 months ago

The problem with this and similar requests is that it would change the identifier scoping in incompatible ways and therefore potentially break a lot of existing SQL code.

zX41ZdbW · 4 months ago

I think it should be not only in GROUP BY, but in every context, e.g., inside expressions in SELECT, WHERE, etc.

kermatt · 4 months ago

PostgreSQL and DuckDB support this, which makes MSSQL feel like a dinosaur in context.

theodpHN · 4 months ago

So, why not a SORT BY ALL or a GROUPSORT BY ALL, too? Not always what you want (e.g., when you're ranking on a summarized column), but it often alphabetic order on the GROUP BY columns is just what the doctor ordered! :-)

petereisentraut · 4 months ago

The working group also discussed ORDER BY ALL, but for some reason most participants really did not like it.

oulipo2 · 4 months ago

Not directly related, but I saw this project recently of a data language by google which is quite cool https://www.malloydata.dev/

cm2187 · 4 months ago

Snowflake has that, once you start using it, it's painful to go back.

sixtram · 4 months ago

What about reusing a CTE? Let me import a CTE definition so that it can be used throughout my app, not just in the current context.

jklowden · 4 months ago

I believe that’s what we call a "view".

chewxy · 4 months ago

BigQuery has that and I've been loving using it since they introduced it

elchief · 4 months ago

duckdb has it

https://duckdb.org/docs/stable/sql/query_syntax/groupby