4 ms·

If you appreciate SQL, the example (update inventory from daily sales) is a thing of beauty.

dtf 6y ago
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.
tgb 6y ago
I'm only passingly familiar with SQL, can anyone explain?
- headalgorithm 6y ago
  Essentially the FROM part calculates how many of each item has been sold today and then uses this amount to reduce how many are currently in stock (the UPDATE and SET parts).
  - tgb 6y ago
    I see what it's doing but I don't really see what the benefit is. Maybe a comparison to what previously had to be done would help me? Would you have to make the 'daily' table in a separate step and then UPDATE using that?
    - nicoburns 6y ago
      Previously you would have to SELECT and then UPDATE in separate queries, meaning that the data is round tripping to your application server and back again. Edit: Or use subqueries apparently.
      - enedil 6y ago
        This is sqlite, you typically don't have a server.
bepvte 6y ago
Is the "set" evaluated where its written or only during "sum()"? If not, whats the sum for?
- dilandau 6y ago
  You can examine the query plan using explain.
brilee 6y ago
It isn't idempotent, though - if you execute the query twice, you're kind of screwed, since your inventory numbers are now bad. A better way to do it would be to create a new table that consists of daily snapshots of inventory, select the latest row, and then use that row's date as a WHERE filter when aggregating the latest transactions.
- chmaynard 6y ago
  > It isn't idempotent, though Agreed, but the purpose of the example is to demonstrate how to use UPDATE FROM, nothing more. I appreciate how it accomplishes a complex update in a very clear, concise, elegant way.

dtf 6y ago

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.

tgb 6y ago

I'm only passingly familiar with SQL, can anyone explain?

headalgorithm 6y ago
Essentially the FROM part calculates how many of each item has been sold today and then uses this amount to reduce how many are currently in stock (the UPDATE and SET parts).
- tgb 6y ago
  I see what it's doing but I don't really see what the benefit is. Maybe a comparison to what previously had to be done would help me? Would you have to make the 'daily' table in a separate step and then UPDATE using that?
  - nicoburns 6y ago
    Previously you would have to SELECT and then UPDATE in separate queries, meaning that the data is round tripping to your application server and back again. Edit: Or use subqueries apparently.
    - enedil 6y ago
      This is sqlite, you typically don't have a server.

bepvte 6y ago

Is the "set" evaluated where its written or only during "sum()"? If not, whats the sum for?

dilandau 6y ago
You can examine the query plan using explain.

brilee 6y ago

It isn't idempotent, though - if you execute the query twice, you're kind of screwed, since your inventory numbers are now bad. A better way to do it would be to create a new table that consists of daily snapshots of inventory, select the latest row, and then use that row's date as a WHERE filter when aggregating the latest transactions.

chmaynard 6y ago
> It isn't idempotent, though Agreed, but the purpose of the example is to demonstrate how to use UPDATE FROM, nothing more. I appreciate how it accomplishes a complex update in a very clear, concise, elegant way.