Querying the max value of a column in SQLite For Android

I have a database created and filed with data. There is a column where i want to find the biggest value.

This is the method used in my database adapter :

public Cursor getBiggestInTheColumn() {
    return db.query(DATABASE_TABLE, null,
                    "MAX(price)", null, null, null, null);
}

It should work but when i call the method :

        cursor = dbadp.getBiggestInTheColumn();

I Get a Runtime Error Like This(LogCat) :

07-14 12:38:51.852: ERROR/AndroidRuntime(276): Caused by: android.database.sqlite.开发者_如何转开发SQLiteException: misuse of aggregate function MAX(): , while compiling: SELECT * FROM spendings WHERE MAX(price)

Any ideas? I suspect this is due to the bad query, but this is the best that i can come up with. Other queries are working well.

If you just want the biggest value then you want the SQL equivalent of: SELECT MAX(price) FROM spendings.

I tend to use db.rawQuery(String sql, String[] selectionArgs), so it would be: db.rawQuery("SELECT MAX(price) FROM spendings", null).

But I think you could do this with db.query(DATABASE_TABLE, new String [] {"MAX(price)"}, null, null, null, null, null);. Though I haven't tested that.

please write the query as select max(price) as price from table name.

resulting cursor follow the below statement

cursor.getString(cursor.getColumnIndex("price"));

Another way to get the column could be to set the query to the max of a particular column, for example:

db.query(DATABASE_TABLE, null, "price=(SELECT MAX(price))", null, null, null, null)

The cursor that returns will be the highest price row in the database.

 public Cursor select(String sql)
{
    try
    {
        Cursor cursor = mDataBase.rawQuery(sql,null);
        return cursor;
    }
    catch (SQLException mSQLException)
    {
        throw mSQLException;
    }
}
and 

call it like:

 Cursor cursor = dal.select("SELECT Max(id) FROM terms"); //where en like '"+name+"%'");
if (cursor != null) {
    cursor.moveToFirst();
   int id= cursor.getInt(0);}

If you only want the biggest INTEGER value then you can use the following:

public int getBiggestInTheColumn() {
    return (int) DatabaseUtils.longForQuery(db, "SELECT MAX(price) FROM " + DATABASE_TABLE, null);
}

DatabaseUtils is added in Android API 1

public static long longForQuery (SQLiteDatabase db, String query, String[] selectionArgs)

Utility method to run the query on the db and return the value in the first column of the first row.

Android Developers > DatabaseUtils

I have same issue in Android. And `db.rawQuery(String sql, String[] selectionArgs), so it would be: db.rawQuery("SELECT MAX(price) FROM spendings", null) - throw Exception.

I fixed by:

db.query(DATABASE_TABLE, null, "price=(SELECT MAX(price) FROM + DATABASE_TABLE + ")", null, null, null, null)

The cursor that returns will be the highest price row in the database. It work correct.

If you want to get max value of a column in a TABLE you can use this method

    public String getMax( String TABLE_NAME, String column_name) {// use the data type of the column
            database.open();
            Cursor cursor = database.query(TABLE_NAME, new String[]{"MAX(" +column_name + ") AS MAX"}, null, null, null, null, null);
            cursor.moveToFirst(); // to move the cursor to first record
            int index = cursor.getColumnIndex("MAX");
            String data = cursor.getString(index);// use the data type of the column or use String itself you can parse it 
            database.close();
            return data;
    }

If you want to get entire record containing the max value for the column

    public List<Object> getMaxRecord(String TABLE_NAME, String column_name) {
            database.open();
            Cursor cursor = database.query(TABLE_NAME, new String[]{"*"}, column_name + " = " + "(SELECT MAX(" + column_name + ") FROM " + TABLE_NAME + ")", null, null, null, null);
            List<Object> demos = cursorToModel(cursor);
            database.close();
            return demos;
    }

note: cursorToModel is a method to get list of records in the cursor satishfying the constraint