6 ms·
Waiting for SQL:202y: Group by All
- Exuma 1y agoAlso just let me reference the damn alias in a group by, FUCK
- mberning 1y agoSome 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 11mo agoAt 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 11mo agoPostgreSQL and DuckDB support this, which makes MSSQL feel like a dinosaur in context.
- zX41ZdbW 11mo agoI think it should be not only in GROUP BY, but in every context, e.g., inside expressions in SELECT, WHERE, etc.
- petereisentraut 11mo agoThe 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 11mo agoLet me reference fields as I create them: select xxxxx as a , a * 2 as b
- zX41ZdbW 11mo agoThis will be great! One of the things ClickHouse has had since 2016.
- deleted 11mo ago[deleted]
- cyberax 11mo agoSQL 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 11mo agoThis 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 11mo agoIdeally, 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.
- 11mo ago
- dorianmariecom 11mo agowould be nice
- cm2187 11mo agoSnowflake has that, once you start using it, it's painful to go back.
- elchief 11mo agoduckdb has it https://duckdb.org/docs/stable/sql/query_syntax/groupby https://duckdb.org/docs/stable/sql/query_syntax/groupby
- oulipo2 11mo agoNot directly related, but I saw this project recently of a data language by google which is quite cool https://www.malloydata.dev/ https://www.malloydata.dev/
- chewxy 11mo agoBigQuery has that and I've been loving using it since they introduced it
- SigmundA 11mo agoSELECT * EXCEPT(col_name) next please.
- azurezyq 11mo agoBigQuery has it! https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select_except https://docs.cloud.google.com/bigquery/docs/reference/standa...
- SigmundA 11mo agoYes it needs to be in the standard though.
- 1718627440 11mo agoThat 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 11mo agoThis was also discussed at the last SQL WG meeting but was postponed for further refinement. But it’s likely to be added soon.
- Inviz 11mo agoWhat's wrong with GROUP BY 1,2,3?
- theodpHN 11mo agoSo, 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 11mo agoThe working group also discussed ORDER BY ALL, but for some reason most participants really did not like it.
- wvbdmp 11mo agoWhat? No! I want GROUP BY * and more importantly GROUP BY mytable.*
- parpfish 11mo agothis 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 11mo agoWhat 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 11mo agoI believe that’s what we call a "view".