Android SQLite take the first element from database column

Question

I am working on a project and created a database with SQLite. In my database I have just two columns, column names are r_id and m_id. I want to take the first element of the r_id and assign it in to a string. The elements of the r_id column is like 1, 2, 3.. in this situation my String has to be 1.

My code; creating a db query: There is no problem I can add data correcly.

my_table = "CREATE TABLE "my_table"("r_id" Text, "m_id" Text);";
db.execSQL(my_table );

Code to take the first element of the column;

public String getSetting() {
            String result = "";
            String[] columns = {"r_id"};
            String[] selectionArgs = {"1"};
            String LIMIT = String.valueOf(1); // <-- number of results we want/expect

            SQLiteDatabase db = databaseHelper.getReadableDatabase();
            Cursor c = db.query(true, "r_id", columns, "row", selectionArgs, null, null, null, LIMIT);

            if (c.moveToFirst()) {
                result = result + c.getString(0);
            } else {
                result = result + "result not found";
            }

            c.close();
            databaseHelper.close();
            return result;
        }

The error I am getting:

android.database.sqlite.SQLiteException: no such column: row (code 1 SQLITE_ERROR): , while compiling: SELECT DISTINCT r_id FROM my_table WHERE row LIMIT 1

Answer 1

The 4th argument of query() is the WHERE clause of the query (without the keyword WHERE) and for it you pass "row".
Also, the 2nd argument is the table's name for which you pass "r_id", but the error message does not contain ...FROM r_id... (although it should), so I guess that the code you posted is not your actual code.

So your query (translated in SQL) is:

SELECT DISTINCT r_id FROM my_table WHERE row LIMIT 1

which is invalid.
But you don't need a WHERE clause if you want just the min value of the column r_id.
You can do it with a query like:

SELECT MIN(r_id) AS r_id FROM my_table

without DISTINCT and a WHERE clause.

Or:

SELECT r_id FROM my_table ORDER BY r_id LIMIT 1;

So your java code should be:

public String getSetting() {
    SQLiteDatabase db = databaseHelper.getReadableDatabase();
    Cursor c = db.rawQuery("SELECT MIN(r_id) AS r_id FROM my_table", null);
    String result = c.moveToFirst() ? c.getString(0) : "result not found";
    c.close();
    databaseHelper.close();
    return result;
}

I used rawQuery() here instead of query().

Or:

public String getSetting() {
    SQLiteDatabase db = databaseHelper.getReadableDatabase();
    Cursor c = db.query(false, "my_table", new String[] {"r_id"}, null, null, null, null, "r_id", "1");
    String result = c.moveToFirst() ? c.getString(0) : "result not found";
    c.close();
    databaseHelper.close();
    return result;
}