SQlite 3.44 is out, and it has some decent features, so I think now it's a good time to show them off. Let's get started!
ORDER BY in aggregates
Aggregate functions now accept an optional
order by after the last parameter. It defines an order in which the function processes its arguments.
order by in aggregates is probably not very useful in
count and other functions whose result does not depend on the order of the arguments. However, it can be quite handy for functions like
Let's say we have an
┌────┬───────┬────────┬────────────┬────────┐ │ id │ name │ city │ department │ salary │ ├────┼───────┼────────┼────────────┼────────┤ │ 11 │ Diane │ London │ hr │ 70 │ │ 12 │ Bob │ London │ hr │ 78 │ │ 21 │ Emma │ London │ it │ 84 │ │ 22 │ Grace │ Berlin │ it │ 90 │ │ 23 │ Henry │ London │ it │ 104 │ │ 24 │ Irene │ Berlin │ it │ 104 │ │ 25 │ Frank │ Berlin │ it │ 120 │ │ 31 │ Cindy │ Berlin │ sales │ 96 │ │ 32 │ Dave │ London │ sales │ 96 │ │ 33 │ Alice │ Berlin │ sales │ 100 │ └────┴───────┴────────┴────────────┴────────┘
Suppose we want to show employees who work in each of the departments, so we group by
department and use
group_concat on the
name. Previously, we had to rely on the default record order. But now we can concatenate the employees in straight or reverse alphabetical order (or any other order — try changing the
order by clause below):
select department, group_concat(name order by name desc) as names from employees group by department;
Now let's group employees by city using
select value ->> 'city' as city, json_group_array( value -> 'name' order by value ->> 'id' ) as json_names from json_each(readfile('employees.json')) group by value ->> 'city'
Better string concatenation
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
select department, string_agg(name, ';') as names from employees group by department;
Now the only black sheep is Oracle, where the poor function is called
Speaking of concatenation, this release brings a couple of scalar string functions:
concat(arg, ...)joins non-null arguments without a separator.
concat_ws(sep, arg, ...)joins non-null arguments using the separator.
select concat_ws(': ', id, name) as employee from employees where city = 'London';
Both functions are also available in PostgreSQL, MySQL and SQL Server.
Thorough date formatting
strftime function was already quite powerful. Now it accepts even more formatting verbs:
%FISO 8601 date: YYYY-MM-DD
%TISO 8601 time: HH:MM:SS
%RISO 8601 time: HH:MM
%Ihour for 12-hour clock: 01-12
%lhour for 12-hour clock without leading zero: 1-12
%khour without leading zero: 0-24
%p"AM" or "PM" depending on the hour
%P"am" or "pm" depending on the hour
%eday of month without leading zero: 0-31
%uday of week 1-7 with Monday==1
select "ISO date" as label, strftime('%F', datetime('now')) as value union all select "ISO time", strftime('%R', datetime('now')) as value union all select "AM/PM time", strftime('%l:%M %P', datetime('now')) as value union all select "Day of month", strftime('%e', datetime('now')) as value union all select "Day of week", strftime('%u', datetime('now')) as value
ISO formatting is most welcome!
If you are wondering why lowercase
p results in uppercase
AM/PM, while uppercase
P results in lowercase
am/pm — it's because the standard C library function
strftime does this (with the
And a few other things
Integrity checking now works with virtual tables.
Full-text search tables can be used in triggers.
There are some query planner optimizations for partial index scans.
And a handful of other improvements that you can read about in the official release notes.
All in all, a great release!
Interactive examples in this post are powered by codapi — an open source tool I'm building. Use it to embed live code snippets into your product docs, online course or blog.
Subscribe to keep up with new posts.