0

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.

Community
  • 1
  • 1
Joshua Dance
  • 8,847
  • 4
  • 67
  • 72
  • 1
    Do you want to just add another column to the table? If so, just include another row in the CREATE TABLE statement – Michael Aug 23 '16 at 16:52
  • 1
    please comment on my answer, I would like to know if this helped solve what you were asking. – Michael Aug 24 '16 at 13:38

2 Answers2

2

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.

Michael
  • 3,093
  • 7
  • 39
  • 83
2

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.

James K. Lowden
  • 7,574
  • 1
  • 16
  • 31
  • Rookie q, but after looking at your example, and trying a few different ways, I still can't get it to work. My question is which element generates the incrementing row number. And how do I use SID in SQLite? Googled it couldn't find anything, and nothing SID related works in my code. – Joshua Dance Aug 24 '16 at 17:15