Waiting for SQL:202y: Group by All

http://peter.eisentraut.org/blog/2025/11/11/waiting-for-sql-202y-group-by-all

59•ingve•2mo ago

Comments

Exuma•2mo ago

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

mberning•2mo 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.

sbuttgereit•2mo 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

kermatt•2mo ago

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

zX41ZdbW•2mo ago

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

petereisentraut•2mo 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.

elygre•2mo ago

Let me reference fields as I create them:

  select xxxxx as a
       , a * 2 as b

zX41ZdbW•2mo ago

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

cyberax•2mo 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•2mo 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.

cyberax•2mo ago

Ideally, it needs to be "from", then arbitrary number of something like `let` statements that can introduce new variables, maybe interspersed with where-s, and then finally "select".

"select" can also be replaced with annotations, something like: `from table_1 t1 let t1.column_1 as @output_1 where ...` and then just collect all the @-annotated variables.

I need to write a lot of SQL, and it's so clumsy. Every time I need a CTE, I have to look into the documentation for the exact syntax.

1718627440•2mo ago

> Ideally, it needs to be "from", then arbitrary number of something like `let` statements

Isn't that what a CTE is?

tracker1•2mo ago

That was kind of my first thought...

cryptonector•2mo ago

Not quite. u/cyberax wants scalar bindings, not table-valued bindings.

Something like

  FROM foo
  LET a = (x + y) * z
  SELECT a;

whereas CTEs are... Common Table Expressions.

snuxoll•2mo ago

WHERE clauses are pushed down into the query planner before the SELECT list is processed, that’s why HAVING exists.

The logical order, in full, is:

FROM

WHERE/JOIN (you can join using WHERE clauses and do FROM a,b still)

SELECT

HAVING

1718627440•2mo ago

That's the order in which the processing happens, but this doesn't need to be reflected in the language. The language has this ordering so it sounds like a natural language which SQL was invented for.

cryptonector•2mo ago

See u/cyberax's comment below. It would be nice to be able to create scalar (as opposed to table-valued) bindings that can be referred to in a WHERE (or JOIN) clause. Currently it's SELECT that establishes such bindings, and... well, it's not terribly clear where they can be used (certainly in HAVING, but first you have to GROUP BY, no?). u/cyberax's idea is to have a LET for this that can come before WHERE and before SELECT.

snuxoll•2mo ago

I mean, I get it, but the big problem is, again, the different phases of execution. The projections you perform with a select can be absolutely arbitrary and do crazy ass things (like do more subqueries that return scalar values, and query planners are notoriously bad at pushing these down), which is why I was trying to say SELECT before WHERE (project before filtering) may be linguistically intuitive, but full of foot guns.

Something like a ‘let’ binding after the FROM/JOIN list would make sense, though - from the query planners perspective it’s nothing more than a token substitution and everything would compile the same.

agnosticmantis•2mo ago

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

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

jiggawatts•2mo ago

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

viraptor•2mo ago

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

cyberax•2mo ago

Thank you! This is indeed close to what I want from SQL!

dorianmariecom•2mo ago

would be nice

cm2187•2mo ago

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

elchief•2mo ago

duckdb has it

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

oulipo2•2mo ago

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

chewxy•2mo ago

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

SigmundA•2mo ago

SELECT * EXCEPT(col_name) next please.

azurezyq•2mo ago

BigQuery has it! https://docs.cloud.google.com/bigquery/docs/reference/standa...

SigmundA•2mo ago

Yes it needs to be in the standard though.

1718627440•2mo ago

That might be nice for manual experimentation, but for application use, this seems brittle compared to specifying the columns you really want to have and process.

petereisentraut•2mo ago

This was also discussed at the last SQL WG meeting but was postponed for further refinement. But it’s likely to be added soon.

Inviz•2mo ago

What's wrong with GROUP BY 1,2,3?

theodpHN•2mo 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•2mo ago

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

wvbdmp•2mo ago

What? No! I want GROUP BY * and more importantly GROUP BY mytable.*

parpfish•2mo ago

this seems to ignore the fact that you can group by a column that isn't in the select statement.

it's not something that i've found a particular use for, but it IS a thing you can do.

sixtram•2mo 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•2mo ago

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

OpenCiv3: Open-source, cross-platform reimagining of Civilization III

The Waymo World Model

How we made geo joins 400× faster with H3 indexes

Unseen Footage of Atari Battlezone Arcade Cabinet Production

Show HN: Look Ma, No Linux: Shell, App Installer, Vi, Cc on ESP32-S3 / BreezyBox

Monty: A minimal, secure Python interpreter written in Rust for use by AI

What Is Ruliology?

Show HN: I spent 4 years building a UI design tool with only the features I use

Microsoft open-sources LiteBox, a security-focused library OS

Sheldon Brown's Bicycle Technical Info

Dark Alley Mathematics

Hackers (1995) Animated Experience

Show HN: If you lose your memory, how to regain access to your computer?

PC Floppy Copy Protection: Vault Prolok

Delimited Continuations vs. Lwt for Threads

An Update on Heroku

How to effectively write quality code with AI

Why I Joined OpenAI

Learning from context is harder than we thought

I spent 5 years in DevOps – Solutions engineering gave me what I was missing

Introducing the Developer Knowledge API and MCP Server

Show HN: R3forth, a ColorForth-inspired language with a tiny VM

Female Asian Elephant Calf Born at the Smithsonian National Zoo

Show HN: ARM64 Android Dev Kit

Understanding Neural Network, Visually

I now assume that all ads on Apple news are scams

Zlob.h 100% POSIX and glibc compatible globbing lib that is faste and better

FORTH? Really!?

Show HN: Smooth CLI – Token-efficient browser for AI agents

WebView performance significantly slower than PWA

OpenCiv3: Open-source, cross-platform reimagining of Civilization III

The Waymo World Model

How we made geo joins 400× faster with H3 indexes

Unseen Footage of Atari Battlezone Arcade Cabinet Production

Show HN: Look Ma, No Linux: Shell, App Installer, Vi, Cc on ESP32-S3 / BreezyBox

Monty: A minimal, secure Python interpreter written in Rust for use by AI

What Is Ruliology?

Show HN: I spent 4 years building a UI design tool with only the features I use

Microsoft open-sources LiteBox, a security-focused library OS

Sheldon Brown's Bicycle Technical Info

Dark Alley Mathematics

Hackers (1995) Animated Experience

Show HN: If you lose your memory, how to regain access to your computer?

PC Floppy Copy Protection: Vault Prolok

Delimited Continuations vs. Lwt for Threads

An Update on Heroku

How to effectively write quality code with AI

Why I Joined OpenAI

Learning from context is harder than we thought

I spent 5 years in DevOps – Solutions engineering gave me what I was missing

Introducing the Developer Knowledge API and MCP Server

Show HN: R3forth, a ColorForth-inspired language with a tiny VM

Female Asian Elephant Calf Born at the Smithsonian National Zoo

Show HN: ARM64 Android Dev Kit

Understanding Neural Network, Visually

I now assume that all ads on Apple news are scams

Zlob.h 100% POSIX and glibc compatible globbing lib that is faste and better

FORTH? Really!?

Show HN: Smooth CLI – Token-efficient browser for AI agents

WebView performance significantly slower than PWA

Waiting for SQL:202y: Group by All

Comments