CODEWITHSUNDEEP
androidsqlite
Falling Into Infinity
Falling Into Infinity

Reputation: 737

Complex queries with SQLite in Android

I have a table with 3 columns "ID", "NAME" & "STATUS". I would like to execute a query on my database where I can get only one entry of "ID" which is located at the top row. I have a working sql query,

"SELECT TOP 1 ID from SAMPLE_TABLE WHERE Status='PENDING' ORDER BY ID ASC;"

This is so far what I implemented in android, 

// Getting pending items
        public int pendingContact() {
            SQLiteDatabase db = this.getWritableDatabase();
            Cursor mCount = db.query(
                    TABLE_CONTACTS ,
                    new String[] { "id" } ,
                    "status = ?" ,
                    new String[] { "PENDING" } ,
                    null ,
                    null ,
                    null 
                );

            mCount.moveToFirst();
            int count = mCount.getInt(0);
            mCount.close();

            return count;
        }

Al through it gives the desired output but I would like to know if there is any other way of doing this more efficiently.

Upvotes: 0

Views: 276

Answers (2)

Steven_BDawg
Steven_BDawg

Reputation: 806

You can use Limit

public Cursor query (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)

limit - Limits the number of rows returned by the query, formatted as LIMIT clause. Passing null denotes no LIMIT clause.

You can do this:

Cursor mCount = db.query(TABLE_CONTACTS, new String[] { "id" }, "status = ?", new String[] { "PENDING" }, null, null, "id ASC", "1");

This will keep you from acquiring more data than you need.

Upvotes: 1

CL.
CL.

Reputation: 180270

When you use ORDER BY, the database will read and sort all PENDING items before it can return the first one.

When using MIN, nothing but the smallest value must be stored temporarily:

SELECT MIN(id) FROM Contacts WHERE Status = 'PENDING'

In code:

Cursor mCount = db.rawQuery("SELECT MIN(id) FROM Contacts WHERE Status = ?",
                            new String[] { "PENDING" });

Upvotes: 0

Related Questions