SQLite 3.44.0 | Hacker News Reader

SQLite 3.44.0

sqlite.org

92 pointsjustinclift10 comments

See also these unofficial interactive release notes: https://antonz.org/sqlite-3-44/ - discussed here: https://news.ycombinator.com/item?id=38095239

My favourite new feature is order by in aggregates, e.g.

    select group_concat(name order by name desc)

Umm, isn't htat what 'over' is for? From memory (prob wrong but close enough)

    select string_concat(name) over (order by name)
    from ...

That's for window functions, which work differently from aggregate functions.

In MS SQL they are used together although I don't know if this is the standard. eg.

    SELECT
        city,  
        STRING_AGG(email,';') 
            WITHIN GROUP (ORDER BY email) email_list
    FROM
        sales.customers
    GROUP BY
        city;

https://www.sqlservertutorial.net/sql-server-string-function...

But even this isn't using the OVER() clause as I remembered. I'm a bit confused about what the standard is, if there is one. Anyway thanks.

I find group concat very useful when I want to aggregate foreign keys as an attribute in the parent row of an 1-N relationship

order by aggregates. I can finally resume work on TreeSQL -- https://github.com/tantaman/TreeSQL :)

WITH RECURSIVE seems OK https://sqlite.org/lang_with.html

It would be helpful if you could put a small, indeed minimal, example of tree SQL on that page, including the schema for data and some idea of the syntax, especially showing where the recursion happens (if it happens). The query currently there is not something I want to fight through, but whatever you're doing sounds interesting.

SQLite is excellent and a very stable software. I really don't follow its development, and settled in an old version.

Sure every problem is different, but what are the really useful features of the recent releases that would make me upgrade?

Not highlighted are bug fixes like this [0] which may have led to this [1] user's malformed database disk image.

0: https://www.sqlite.org/src/info/c9fdd6805df04f05

1: https://github.com/vlcn-io/js/issues/31

Sure Sqlite is stable, but everything is relative.

Reply on news.ycombinator.com