How to select table rows in the reverse order of insertion in SQLite?

Question

Hi i want to retrieve the rows from a table in the reverse order in which it is inserted.

I know we can use integer primary key and sort with that, but since am using it in an android application. It is hard to change the table structure for already existing users. (Ya i forget to give an integer primary key in older version)

After searching a lot i found that there is a secret column in Sqlite called ROWID and we can use it for sorting.

SELECT * FROM tablename ORDER BY rowid DESC

But even after using it, i got shuffled result.

Any possible solutions or ideas?

Answer 1

There is no guarantee, unless you include a column specifically for this purpose. However it is pretty likely that rowid may be in the correct order and therefore that

`SELECT * FROM tablename ORDER BY rowid DESC`

will result in the rows being in/or close to the reverse order.

Basically the algorithm for determining the rowid to use, is to find the highest currently used rowid and add 1. However, if the rowid has reached 9223372036854775807 (the highest number that a rowid can be), then SQLite will have an attempt at finding an unused number and use that. Hence the no guarantee.

• note, if the AUTOINCREMENT keyword has been used then if the rowid (the PRIMARY KEY column is then an alias of the rowid column) has reached 9223372036854775807, then no additional rows can be added. An attempt to do so will result in an SQLITE FULL error.

Considering your comment re having a column already defined as INTEGER PRIMARY KEY, then this is an alias for the rowid if you change a value in that column then the rowid will be changed. Which appears to be what you are experiencing.

Consider the following :-

DROP TABLE IF EXISTS testtable;
CREATE TABLE IF NOT EXISTS testtable (mycolumn TEXT, nmbr INTEGER, nottherowid INTEGER PRIMARY KEY);
WITH RECURSIVE crtdata(tags,rnd) AS (
    SELECT 1, random()  UNION ALL SELECT tags+1, random() FROM crtdata LIMIT 30) -- creates 30 rows for insertion
    
    
INSERT INTO testtable (mycolumn, nmbr) SELECT * FROM crtdata; -- <<<< Load the table 

SELECT rowid AS the_rowid,* FROM testtable ORDER BY rowid ASC; -- <<<< sort in insertion order (very likely)

UPDATE testtable SET nottherowid = nottherowid + 10000 WHERE (nottherowid % 4) = 0; -- change every 4th row so alias of rowid is + 10000
UPDATE testtable SET rowid = rowid + 5000 WHERE (rowid % 5) = 0; -- <<<< change every 5th row so rowid is + 5000 

SELECT rowid AS the_rowid,* FROM testtable ORDER BY rowid ASC; -- <<<< ooops now not in insertion order

Then the first result will be in insertion order, as per :-

But the second result will not be, as per :-

• Noting that mycolumn reflects the original rowid/INTEGER PRIMARY KEY column (aka nottherowid column)