How To Get A Row In SQLite By Index (not By Id)

Is there a way to get a row by its index/position in table, rather than by its id? For example I have 3 rows with IDs: Record1 Record2 Record3 Now if I delete the second row using

Solution 1:

The better route to go will be to fetch the id of the record from the object represented by ListView item and then use that to get the correct record in the database. In your ListView's OnItemClickListener, the onItemClick event takes the AdapterView as the first argument and the index of the selected item as the second. Get that item from the adapter and cast it to the type it represents.

public void onItemClick(AdapterView<?> arg0, View arg1, int arg2, long arg3) {
    YourClass c = (YourClass)arg0.getItemAtPosition(arg2);
    //index of the record to delete can now be accessed at c.id
}

However, if you really want to get the nth record, I believe you can do the following:

SELECT * FROM TableName LIMIT 1 OFFSET n;

Where n is the index you're after. This also assumes that your results are ordered in the same fashion as they are in your ListView.

Solution 2:

SQLite3 has been greatly improved since the introduction of Android OS. From the official documentation (and in my own words):

All rows within SQLite tables have a 64-bit signed integer key that uniquely identifies the row within its table. This integer is usually called the "rowid". The rowid value can be accessed using one of the special case-independent names: rowid, oid, or _rowid_, in place of a column name. (In the early Android SQLite implementations you had to use _id to access the index.)

So today you can do:

    SELECT rowid,* FROM tt LIMIT 1 OFFSET 2;
    SELECT rowid,* FROM tt WHERE rowid=2;
    SELECT rowid,* FROM tt WHERE rowid=(SELECT MAX(rowid) FROM tt); -- to get last row index