sqlsqlitesql-insert

Proper way to locate the position of a single newly inserted row based on order by clause immediately after insert?

After inserting a single row of data into a table (always a single row), I'd like to return to the application code the relative position of that row based on an order by clause. Thus, the application code sends SQLite a row to insert and SQLite returns its position.

Using last_insert_rowid() and the window function row_number() appears to work but that's just my hackish guess. Are there better, more efficient methods?

If I copied this correctly, this example runs in the SQLite Fiddle.

create table data (key,value);

insert into data 
values (5, 'e'), (3, 'c'), (8, 'h');

select rowid, * from data;

+-------+-----+-------+
| rowid | key | value |
+-------+-----+-------+
| 1     | 5   | e     |
| 2     | 3   | c     |
| 3     | 8   | h     |
+-------+-----+-------+

insert into data values (1, 'a');

select * from data order by value asc;

+-----+-------+
| key | value |
+-----+-------+
| 1   | a     |
| 3   | c     |
| 5   | e     |
| 8   | h     |
+-----+-------+

select d.*
from 
    (select rowid as row_id, row_number() over (order by value asc) pos, * 
     from data) d
where d.row_id = last_insert_rowid();

+--------+-----+-----+-------+
| row_id | pos | key | value |
+--------+-----+-----+-------+
| 4      | 1   | 1   | a     |
+--------+-----+-----+-------+

insert into data values (2,'b');

select * from data order by value desc;

+-----+-------+
| key | value |
+-----+-------+
| 8   | h     |
| 5   | e     |
| 3   | c     |
| 2   | b     |
| 1   | a     |
+-----+-------+

select d.*
from 
    (select rowid as row_id, row_number() over (order by value desc) pos, * 
     from data) d
where d.row_id = last_insert_rowid();

+--------+-----+-----+-------+
| row_id | pos | key | value |
+--------+-----+-----+-------+
| 5      | 4   | 2   | b     |
+--------+-----+-----+-------+
Solution

  • Your last_insert_rowid() is the right solution (for a 1 row insert):

    Exploring for multiple values

    In case you wanted to expand it to multiple values, SQLite's returning looks promising, however it has a limitation (as of 3.48.0):

    You cannot rely on an all-inclusive with inserts as (insert into data values (…) returning rowid) select …;, as:

    We could try "preparing" our data to insert in a CTE, compute its position in the projected data (select * from data union all select * from this cte), insert from this future data enriched with pos… but then returning won't allow outputting pos (neither directly, nor by select key, value, pos from data2 union all insert … returning key, value, null).

    We cannot create table inserted as insert … returning rowid.

    Remember in trigger by inserting to a log

    The only solution I found using only SQL was to insert to another (non temporary) table from a trigger,
    then fetch back from this table.

    This table could be truncated before each new run, so that it only contained the really last inserted ids, however this would prevent concurrent connections inserting in the same database.

    To mitigate it we should have a "session ID" (which could be $$ for CLI sqlite3, or thr_self() / gettid() from a C program), and get only the rows corresponding to our current thread / process.
    Another way is to timestamp rows of this log, and get only rows that have the same timestamp as last_insert_rowid(). However keep in mind that this will only reduce the risk of multiple connections mixing their last insert, not remove it (particularly as SQLite 'subsec' date modifier only goes down to the millisecond).

    create table watcher (t, rowid);
create trigger watcher
after insert on data
begin
    insert into watcher values (datetime('subsec'), new.rowid);
end;

insert into data values (10, 'p'), (11, 'q');

select d.*
from (select rowid as row_id, row_number() over (order by value desc) pos, * from data) d
where d.row_id in (select rowid from watcher where t = (select t from watcher where rowid = last_insert_rowid()));

    (all attempts are shown in this fiddle)

    So this is still an imperfect solution, and the simplest way to get the position of rows inserted by your connection would probably be to…

    Get your wrapper program to intercept returning rowid and reuse it in a new select

    Even in shell!

    … with a bit of searching input for predefined column names last_row_id and last_row_ids:

    insert into data values (12, 'n'), (13, 'm') returning rowid as last_row_id;

select d.*
from (select rowid as row_id, row_number() over (order by value desc) pos, * from data) d
where d.row_id in (last_row_ids);

    #!/bin/sh

set -e

fifo=/tmp/temp.$$.last_row_ids
mkfifo $fifo

while read l
do
    # A query needing last_row_ids? Wait for the temp file to be filled by the output watcher proc, then replace last_row_ids by the contents of the file:
    case "$l" in
        *last_row_ids*)
            read last_row_ids < $fifo
            l="`echo "$l" | sed -e "s/last_row_ids/$last_row_ids/g"`"
            ;;
    esac
    echo "$l"
    # After a list of new rowids, add a "complete" tag:
    case "$l" in
        *"returning rowid as last_row_id"*)
            echo "select '' as last_row_id_end;"
            ;;
    esac
done < /tmp/2.sql |
sqlite3 |
{
    # Watcher proc, that looks for sqlite3 outputting our predefined columns:
    store=
    while read l
    do
        # End of output for our last_row_id column? Write the intercepted ids to the temporary file, and quit store mode.
        case "$l" in
            last_row_id_end)
                echo "$store" > $fifo
                store=
                continue
                ;;
        esac
        # Store mode: store one more rowid:
        case "$store" in
            .) store="$l" ;;
            ?*) store="$store,$l" ;;
        esac
        # If we get output of only one column named last_row_id, start to store:
        case "$l" in
            last_row_id) store=. ;;
        esac
        echo "$l"
    done
}
rm -f $fifo