When getting the “next” or “previous” entry in an SQLite table, I’ve been using the rowId so far and it’s been okay.
Recently, though, it’s not been that easy. For example, when a user imports a set of entries, some of which have an earlier timestamp than the current entries, they end up in the middle of the entries but their rowIds are newer and so are considered to be at the end of the list.
This causes weird navigation issues, so it’s best to stick with a single way to order entries.
Here is a sample of a SELECT statement that will get a rowId of the next entry in order of timestamp:
|
1 2 3 4 |
SELECT DISTINCT rowid FROM entry_table WHERE timestamp > 2010-09-15 13:15:20.250 ORDER BY timestamp ASC LIMIT 1 |
However, this causes an SQLite exception in Android/Java and other errors in other SQLite implementations. The problem is the space in the timestamp given. Whenever there is a space there should be quotation marks surrounding the piece of text.
So the timestamp should be “2010-09-15 13:15:20.250″, giving a SELECT statement that looks like this:
|
1 2 3 4 |
SELECT DISTINCT rowid FROM entry_table WHERE timestamp > "2010-09-15 13:15:20.250" ORDER BY timestamp ASC LIMIT 1 |
This selects one row where the timestamp is the next greatest than the given one, i.e. the one right after the given one. It should also be error-free, now that we’ve included the quotation marks.