2

Let's say I make 5 entries in a sqlite db.

_id | Name
1    Tom
2    Oskar
3    Matt
4    Mike
5    Ed

If I delete number 4 and 5, I only have 1-3. I then do a max _id query then I get number 3. But, of course I want the next auto incremented _id. What would be the method to do to get the number "6" in this example?

EGHDK
  • 17,818
  • 45
  • 129
  • 204
  • 1
    So why don't you make it auto-incremented. Then you don't have to worry about it. – Scorpion Oct 21 '12 at 09:06
  • You might want to read [this](http://www.sqlite.org/autoinc.html) and [this](http://stackoverflow.com/questions/2127138/how-to-retrieve-the-last-autoincremented-id-from-a-sqlite-table) links for your answer – Sujay Oct 21 '12 at 09:11
  • It is autoincrement. But, I have a program that needs to know the next id, because when I use max id, my program is thrown off track. – EGHDK Oct 21 '12 at 09:14
  • 1
    If you are relying on the auto increment _id somewhere else in your code, then sorry, but your design is wrong. The purpose of the _id field is for when you cannot, or do not need, a primary key across your columns. – Simon Oct 21 '12 at 10:00
  • I'm using it with conjunction with something else, so it's a lot more difficult than it seems. – EGHDK Oct 21 '12 at 10:05

1 Answers1

5

If your _id column has type INTEGER PRIMARY KEY AUTOINCREMENT, you can read the current sequence value from the sqlite_sequence table, and thus determine the next one:

SELECT seq + 1 FROM main.sqlite_sequence WHERE name = 'mytablename'
CL.
  • 173,858
  • 17
  • 217
  • 259