4 ms·

Is “GROUP BY 2” a shorthand for “GROUP BY itemId”, the second column in the FROM SELECT? Not sure I’ve seen that syntax before.

• tmoertel 6y ago
  Yes. GROUP BY n refers to the nth column of the result set.
• barrkel 6y ago
  You can mention columns from the select clause by ordinal in group by and order by clauses, in lieu of restating the full expression. Very helpful with complex expressions, but IMO not the greatest for readability, so I tend to only use it in ad hoc queries. It's part of SQL-92, but I believe it is deprecated. Many interpreters let you use a column alias from the select clause in the group and order clauses. This has better readability IMO but I'm not sure it's in the SQL-92 standard, but I believe it is now standardized.
  • nicoburns 6y ago
    > Many interpreters let you use a column alias from the select clause in the group and order clauses. This has better readability IMO but I'm not sure it's in the SQL-92 standard, but I believe it is now standardized. Not sure if it's standardised, but Postgres doesn't let you do this (rather irritatingly).
    • formerly_proven 6y ago
      Yes, it's not in the standard. IIRC they only don't do it because it's not in the standard, somewhat annoying for iterating on queries. In stuff like SQLAlchemy it's not as annoying, since you can write it DRY.
      • barrkel 6y ago
        I believe it's in SQL:1999. It's tedious that all this is hearsay without open access standards. It's "only" $195 for the latest.
    • benesch 6y ago
      No, PostgreSQL definitely lets you do this. > An expression used inside a grouping_element can be an input column name, or the name or ordinal number of an output column (SELECT list item), or an arbitrary expression formed from input-column values. [0] > Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values. [1] Perhaps you are thinking of trying to use an output column name in a WHERE clause? [0]: https://www.postgresql.org/docs/current/sql-select.html#SQL-GROUPBY https://www.postgresql.org/docs/current/sql-select.html#SQL-... [1]: https://www.postgresql.org/docs/current/sql-select.html#SQL-ORDERBY https://www.postgresql.org/docs/current/sql-select.html#SQL-...
      • nicoburns 6y ago
        > Perhaps you are thinking of trying to use an output column name in a WHERE clause? Yeah, I think I am thinking about this. Its pretty frustrating because the actual query engine is perfectly capable of executing fairly complex SQL expressions efficiently (they're not really that complex computationally, but they are syntactically because of SQL's verbosity), but the code becomes quite unmaintainable if you use too many of them. For example, the following expression: ROUND(( (EXTRACT(EPOCH FROM (s.end_time - s.start_time)) / 60) -- Shift length in minutes - (FLOOR((EXTRACT(EPOCH FROM (s.end_time - s.start_time)) / 60) / 380) * 20) -- Break length in minutes )::numeric / 60 , 2) as hours_planned, It repeats the sub-expression `EXTRACT(EPOCH FROM (s.end_time - s.start_time)) / 60)`. If I could name that sub expression and reference it multiple times then the overall expression would be a lot more readable.
• cabaalis 6y ago
  Order by [column number] is also a useful shortcut in mssql. Very often my columns are a compilation of functions, it feels clean to not repeat them. I'm not sure of availability in other sqls.
  • nogabebop23 6y ago
    IMO it looks cleaner but adds cognitive overhead for readability so I'm not sure if it is actually better. Feels kind of like a lookup table that a reader now has to reference. YMMV
    • sroussey 6y ago
      In code, I agree. But when you are a REPL, it is handy to have shortcuts.