Hacker News Reader

Comment by chmaynard | Hacker News Reader

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

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.

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.

> 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).

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-...

[1]: https://www.postgresql.org/docs/current/sql-select.html#SQL-...

> 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.

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.

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.

Yes. GROUP BY n refers to the nth column of the result set.

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.

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

In code, I agree. But when you are a REPL, it is handy to have shortcuts.

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.

> 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.

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

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).

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?

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.

This is sqlite, you typically don't have a server.

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

Reply on news.ycombinator.com