CODEWITHSUNDEEP
sqliterow-number
Joshua Dance
Joshua Dance

Reputation: 10512

Easy, computationally cheap way to add a row number to a table using SQLite?

Googled this quite a bit and found answers asking similar but different questions, but not what I am looking to do.

I have a table, and I want to add a row number to it. So:

ID  || value 
91  || valueA
11  || valueB
71  || valueC

becomes

Row# || ID  || value 
1    || 91  || valueA
2    || 11  || valueB
3    || 71  || valueC

Found this answer that is a bit more complex than my use case. Also I was warned against using the answers at they are computationally expensive (n^2-ish).

Also found a few other answers like this one where the user wanted the row number returned for a query, but that is a different use case. I just want to append a row number to all the rows in the table.

Upvotes: 0

Views: 309

Answers (2)

James K. Lowden
James K. Lowden

Reputation: 7837

As I think you know, in SQL tables have no inherent order. Therefore any "row number" is based on some implicit order. If you make use of any kind of "row id" in the DBMS, the implicit order is likely to be insertion order. That's cheap, if if it suits your needs, that's what you want.

Any other "row number" you create requires a sort; if supported by an index, that sort will be O(N log N), else, yes, O(N^2). The math has a way of being very insistent about that.

After answering this question many times in different guises, I wrote a simple example. In SQLite I've had good experience with under a million rows. Larger sorts take longer, YMMV.

FWIW, I never store derived order. Because the system can't feasibly enforce its correctness, it's impossible to know if it's correct. Better to keep a covering index on the interesting order, and rely on a view to supply rank ordinals when needed.

Upvotes: 2

Michael
Michael

Reputation: 3239

Based on your question, it seemed like you were asking for another column in your database. If that's not the case please comment.

In your database creation class (or wherever you create your database), in the CREATE TABLE statement, use the following structure:

CREATE TABLE table_name(
    RowNum INTEGER AUTOINCREMENT,
    _ID INTEGER PRIMARY KEY,
    value TEXT,
);

Increment the database version by 1 and it'll be good to go.

Upvotes: 2

Related Questions