Monday, April 23, 2012

Select top N rows in Android SQLite

I had heard a lot about SQLite which is embeddable and small makes it suitable for mobile devices. But never got a chance to work on SQLite until I started Android. Setting up the data base and tables are little lengthy .So may be blog about those details later. This post is to note down a difference in using normal SQL and SQLite to fetch the top n rows.
The scenario was simple. We are showing some notifications in the app which are periodically syncing with the database using WCF services. The Android app uses SQLite to store the notification locally so that users will never miss any notification. It requests the latest notifications by sending the id of last notification it has. Its simple to fetch the highest id of notification rows using the normal SQL .The normal SQL query will look as follows
--Don't look at the * in query.Its sample
Select Top 1 * 
From Notifications
Order By Id

But the top is not supported in SQLite. We need to use the other keyword limit. It just limits the number of rows fetched

public Cursor select(String condition,String orderBy,String limit,SQLiteDatabase db){
    String qry=String.format("select * from %s order by %s desc limit %s", TABLE_NAME,orderBy,limit);
    return db.rawQuery(qry,null);

Interested in SQLite ? There are some more limits to SQLite.For more details refer the below.

No comments: