Wednesday, 3 August 2011

SQLite - Getting the last row of a table

Sometimes it is requieres to get the last row of the table. The easier way is by knowing what is the last number of rows and then use a sentence to get the value from that row.


1. Get number the last row of a table
If you are using the autoincrement (something like: _id integer primary key autoincrement), sqlite creates a special table called sqlite_sequence which keep track of the last row of all the tables. This meta table contains two columns: name and seq. Then, the sqlite sentence to get the value of a table is:


SELECT seq from SQLITE_SEQUENCE WHERE name='name_of_the_table';

For example, if the name of the table is "animals", it could be:


SELECT seq from SQLITE_SEQUENCE WHERE name='animals';

Note that for the name of the table I am using a single quote. If you are using double quote it could works also.

2. Get the content of the row
Once the number of the last row is known, the last sentence to get the data could be:

SELECT * from name_of_the_table where _id=last_row;

For more information about how to use sqlite, here is a quite good tutorial:

1 comment: