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.<p>Other niche but useful features I've found with GROUP BY:<p>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.<p>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`<p>[1]: <a href="https://www.sqlite.org/lang_aggfunc.html" rel="nofollow noreferrer">https://www.sqlite.org/lang_aggfunc.html</a><p>[2]: <a href="https://www.sqlite.org/lang_select.html#bare_columns_in_an_aggregate_query" rel="nofollow noreferrer">https://www.sqlite.org/lang_select.html#bare_columns_in_an_a...</a>
From the full release notes:<p>> The PRAGMA case_sensitive_like statement is deprecated, as its use when the schema contains LIKE operators can lead to reports of database corruption by PRAGMA integrity_check.<p>Without enabling that pragma, you get the default behavior where 'a' LIKE 'A' is true.<p>This is unfortunate, since case-sensitive comparisons seem like an important tool.<p>I'm not sure there's another built-in option for this. SQLite doesn't ship with a regex engine (you have to wire up an application-defined SQL function, if your driver supports it).
> Aggregate functions can now include an ORDER BY clause after their last parameter.<p>Hopefully that means we'll soon get support for `DISTINCT` in `GROUP_CONCAT`.<p>Atm you can do `group_concat(x, '|')` and `group_concat(distinct x)`, but you can't do `group_concat(distinct x, '|')`.
I found SQLite grow bigger and bigger:
- For 3.7.8, its default build on x86 is only 350KiB (1).
- Now it is 650 KB (2).<p>This is really worrying, especially for the field of embedded development.<p>[1] <a href="https://www.hwaci.com/sw/sqlite/footprint.html" rel="nofollow noreferrer">https://www.hwaci.com/sw/sqlite/footprint.html</a>
[2] <a href="https://www.sqlite.org/footprint.html" rel="nofollow noreferrer">https://www.sqlite.org/footprint.html</a>
> <i>As you probably know, the group_concat function is SQLite (and MySQL) specific. In other databases (namely, PostgreSQL and SQL Server) the function is called string_agg. As of 3.44, SQLite also supports string_agg as an alias for group_concat(expr, sep) (...) Now the only black sheep is Oracle, where the poor function is called listagg.</i><p>Minor nitpick, but why not go all the way and add the Oracle syntax while they were at it? Is there a technical reason not to? (I've never used Oracle)
Is there any chance SQLite will sometime work stored under CIFS/SMB shares? I wanted to store all my container /config directories under NAS but can't with the ones using SQLite.