SQLite Forum

Keith, you have provided excellent hints, which drove me to a solution that sounds quite general and satisfying.

It uses window functions, so it requires SQLite 3.28+.

It looks like it has a good complexity, as I see by running the request on datasets of various sizes. For a constant number of posts per chats, it is linear with the number of chats. For a constant number of chats, it is linear with the number of posts per chats. This sounds quite acceptable to me, as it fits well a kind of database that is quite common, in the wild.

It is *general enough*, because it accepts the input I have:

- one parent table (here, "chat")
- one child table (here, "post")
- a foreign key from child to parent
- a set of ordering clauses on the child table, which identifies one child per parent. The ordering clauses may not identify a single child per parent. The set of ordering clauses may actually be empty.

So, the SQL:

    WITH latestPost AS (
        SELECT chatId, id AS postId FROM (
            SELECT chatId, id, RANK() OVER (
                PARTITION BY chatId
                ORDER BY
                    -- Any number of user-provided ordering clauses
                    date DESC,
                    -- When user-provided ordering clauses are not unique,
                    -- append a primary key ordering for disambiguation
                    -- and guarantee that there is a single row with rank 1.
                    id)
                    AS _rank
            FROM post)
        WHERE _rank = 1
    )
    SELECT chat.*, post.*
    FROM chat
    LEFT JOIN latestPost ON latestPost.chatId = chat.id
    LEFT JOIN post ON post.id = latestPost.postId;

The query plan:

    QUERY PLAN
    |--MATERIALIZE 1
    |  |--CO-ROUTINE 4
    |  |  |--SCAN TABLE post USING COVERING INDEX post_index
    |  |  `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
    |  `--SCAN SUBQUERY 4
    |--SCAN TABLE chat
    |--SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (_rank=? AND chatId=?)
    `--SEARCH TABLE post USING INTEGER PRIMARY KEY (rowid=?)