0

I'm trying to get the the ten latest entries in my sqlite database. I need to fetch only one entry at a time instead of those ten entries at once.

Until now, i was doing this:

"SELECT " + sql + " FROM " + TABLE_NAME
                    + " WHERE _id = (SELECT max(_id)-2 FROM " + TABLE_NAME + ")";

For every entry i need, i was just doing ...max(_id)-x but this does not work, if some entries are deleted at some time. The id's have some gaps (like 1, 2, 4, 8, ...).

So after some research i was trying something like this:

    "SELECT * FROM (SELECT " + sql + ", rank() over (order by max(_id) desc) rk 
FROM " + TABLE_NAME + " GROUP BY " + sql + ") t WHERE rk = 2"

this approach however crashes with the following message

android.database.sqlite.SQLiteException: near "(": syntax error (code 1): , while compiling: select * from (select price, rank() over (order by max(_id) desc) rk from refuels group by price) t where rk = 2

How can I get this done? Thanks in advance

Nizam
  • 5,698
  • 9
  • 45
  • 57
mike.b93
  • 1,989
  • 2
  • 18
  • 31
  • Have you looked into this here: http://stackoverflow.com/questions/14018394/android-sqlite-query-getting-latest-10-records? – Ben Dec 25 '13 at 13:25
  • @Ben alright, but how would i pick one of the ten entries? – mike.b93 Dec 25 '13 at 13:28
  • What do you mean by how to pick one? http://www.vogella.com/articles/AndroidSQLite/article.html is a nice tutorial for sqlite – Ben Dec 25 '13 at 13:36
  • when i query like in that answer, don't i get all ten latest entries returned? I need to pick one of the ten entries. – mike.b93 Dec 25 '13 at 13:38
  • Yes, your result will be 10 results (if there exist at least 10 entries in your table). Check out the tutorial I provided above - theres explained how to deal with the results of a query. – Ben Dec 25 '13 at 13:43
  • http://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ – Ben Dec 25 '13 at 14:31

1 Answers1

0

I found a simple and easy solution:

"SELECT " + sql + " FROM " + TABLE_NAME + " ORDER BY _id DESC LIMIT 1 offset n"

where the n is the number of the entry -1. Meaning, I want row number 6, n has to be 5.

mike.b93
  • 1,989
  • 2
  • 18
  • 31