Comment by matharmin | Hacker News Reader

Comment by matharmin | Hacker News Reader

I can definitely see where ORDER BY in aggregates would be useful - I recently used it with Postgres, and didn't even know it's not available in SQLite. Often you can do the sorting in the app code, but it's nice to do directly in the query.

Other niche but useful features I've found with GROUP BY:

1. You can filter the rows used for the aggregate using a FILTER clause [1]: `group_concat(name order by name desc) filter where(salary > 100)`. While you could do the same filter in the main query, this form would still give you departments where all employees are filtered out.

2. When selecting non-aggregate values in a GROUP BY, an arbitrary row is typically picked. You can control which one is picked by using min() or max() on a different column [2]. Example: `select depertment, name as best_earner, max(salary) as best_salary from employees group by department`

[1]: https://www.sqlite.org/lang_aggfunc.html

[2]: https://www.sqlite.org/lang_select.html#bare_columns_in_an_a...

Surprised they let you pick something without defining the aggregate as it seems really dangerous.

Not entirely sure how far it extends but I’ve noticed that Postgres will let you select any rows from the table that way if you’ve included the primary key in the group by. Back when I used sql server / MySQL you needed to specify them all in the group by.

SQL Server forces determinism here: anything projected when there is a GROUP BY must be one of the grouping properties, an aggregate, or a constant.

mySql does allow other columns to be projected, which has always felt wrong to me as I like my results from the DB to be dertemanistic. I wasn't aware sqlite allowed it too, though I've not directly used sqlite much. It is something that causes issues migrating to other DBs (or when trying to be cross-DB compatible). Often it is used accidentally and by chance gives good results, where is is used deliberately IME it is where the same value will always return due to properties of the that data being queried (in which case the "fix" for other DBs is to use an aggregate like MIN or MAX). Sometimes accidentally use leads to subtle bugs which, as you say, can be rather dangerous.

Note though that these days this can only be used dangerously if the ONLY_FULL_GROUP_BY option is on, and it is off by default. Allowing it in safe circumstances actual makes modern mySql more standards compliant in this respect, see https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.ht... for more detail.

Sometimes I'd like to pick a non-aggregated, non-grouped column that I know will be the same for the whole group. (I often write SQL in GCP's BigQuery that forbids indeterminate results in group bys) I'd like to have an aggregate function something like assert_same(), that picks the value, and errors when some of the values in the group are unexpectedly different.

You can do that with standard aggregates by comparing min() and max().

In postgres you can array_agg(distinct) and error on the application side if there is more than one value.

Or sometimes you can do something like FIRST() OVER (ORDER BY NULL).

I don't think that errors if the group contains different values...?

Snowflake has ANY_VALUE() for this: https://docs.snowflake.com/en/sql-reference/functions/any_va...

Reply on news.ycombinator.com