CODEWITHSUNDEEP
androidsqlite
user9216568
user9216568

Reputation: 81

SQLite getting the row with the max value

So I have a filled in Database with the columns: _ID, excersise, reps and timestamp. Im trying to print out the row with the highest rep number of an excersise with this Cursor:

private Cursor getRepRecord(String excersise) {
    return myDatabase.query(Contact.ExcersiseEntry.TABLE_NAME,
            new String [] {"MAX(reps)"},
            Contact.ExcersiseEntry.EXCERSISE_NAME + "= '" + excersise + "'",
            null,
            null,
            null,
            Contact.ExcersiseEntry.EXCERSISE_REPS + " DESC");
}

and then I use this method to print the cursor rows:

private void getEntryFromDatabase(Cursor cursor) {
    cursor.moveToFirst();

    while (!cursor.isAfterLast()) {
        String excersise = cursor.getString(cursor.getColumnIndex(Contact.ExcersiseEntry.EXCERSISE_NAME));
        int reps = cursor.getInt(cursor.getColumnIndex(Contact.ExcersiseEntry.EXCERSISE_REPS));
        int id = cursor.getInt(cursor.getColumnIndex(Contact.ExcersiseEntry._ID));

        Log.i("Entry", "ID: " +id  + " || Excersise: " + excersise + " || reps: " + Integer.toString(reps));

        cursor.moveToNext();
    }
}

How ever I get the Error: CursorWindow: Failed to read row 0, column -1 from a CursorWindow which has 1 rows, 1 columns. I know there are alot of similar questions but I looked at man and still couldn´t find the Solution...

Upvotes: 0

Views: 439

Answers (1)

MikeT
MikeT

Reputation: 57063

The reason why you are getting the -1 is because the columns you are trying to extract data from do not exist in the Cursor (the getColumnIndex method returns -1 if the column cannot be found).

The Cursor will only have a single column named MAX(reps).

You can easily add all the other columns by adding * (separated from the MAX(reps) column by a comma or you could add other columns individually as elements of the array. If you want to display the maximum reps you would extract the column named MAX(reps) or you could rename the column using AS e.g. MAX(reps) as maxreps

So you could have :-

private Cursor getRepRecord(String excersise) {
    return myDatabase.query(Contact.ExcersiseEntry.TABLE_NAME,
            new String [] {"MAX(reps) AS maxreps", *}, //<<<< Changed
            Contact.ExcersiseEntry.EXCERSISE_NAME + " = '" + excersise + "'",
            null,
            null,
            null,
            Contact.ExcersiseEntry.EXCERSISE_REPS + " DESC");
}

This could be used in conjunction with a slightly amended getEntryFromDatabase method :-

private void getEntryFromDatabase(Cursor cursor) {
    //cursor.moveToFirst(); //<<< does nothing of any use as return value is ignored

    while (!cursor.isAfterLast()) {
        String excersise = cursor.getString(cursor.getColumnIndex(Contact.ExcersiseEntry.EXCERSISE_NAME));
        int reps = cursor.getInt(cursor.getColumnIndex(Contact.ExcersiseEntry.EXCERSISE_REPS)); // Would this be of any use???
        int id = cursor.getInt(cursor.getColumnIndex(Contact.ExcersiseEntry._ID));
        int maxreps = cursor.getInt(cursor.getColumnIndex("maxreps")); //<<<< Added

        Log.i("Entry", "ID: " +id  + " || Excersise: " + excersise + " || reps: " + Integer.toString(reps) + " || maxreps: " + Integer.toString(maxreps);    
        cursor.moveToNext();
    }
}

EDIT re comment :-

I still don´t quite understand why. The correct SQL term would be something like SELECT * WHERE reps = max(reps), right? How does it translate into the Max(reps), *

If you used SELECT * FROM reps WHERE reps = Max(reps) it would return all defined columns (the * translates to all columns) for the row or rows that is/are equal to highest rep value (note see below why this would work anyway). Which could be what you want. (ORDER BY reps DESC (or ASC) is irrelevant).

The list of columns after SELECT (SELECT ALL or SELECT DISTINCT) defined the result_columns i.e. the columns that will exist in the resultant Cursor. If you said SELECT reps FROM reps then the resultant cursor would have just the 1 column called reps. SELECT reps, exercise then the resultant cursor would have two columns.

SQL allows derived columns (my term). The derived column name will take the name of the expression used to derive the value. So if you say SELECT max(reps) FROM reps then the result will be a Cursor with 1 column named max(reps) (and because MAX is an aggregate function 1 row (unless GROUP BY is used)).

The query method used (there are 4 in total) in your code has the signature :-

Cursor query (String table, 
                String[] columns, //<<<< list of result columns
                String selection, //<<<< WHERE CLAUSE
                String[] selectionArgs, 
                String groupBy, 
                String having, 
                String orderBy)

So :-

myDatabase.query(Contact.ExcersiseEntry.TABLE_NAME,
            new String [] {"MAX(reps)"},
            Contact.ExcersiseEntry.EXCERSISE_NAME + "= '" + excersise + "'",
            null,
            null,
            null,
            Contact.ExcersiseEntry.EXCERSISE_REPS + " DESC");

results in the SQL SELECT MAX(reps) FROM reps WHERE excercise = 'your_excercise';

So the resultant Cursor will have 1 column named MAX(reps).

If you wanted SELECT * FROM reps WHERE reps = MAX(reps) then you'd use :-

myDatabase.query(Contact.ExcersiseEntry.TABLE_NAME,
            null, //<<<< ALL columns
            Contact.ExcersiseEntry.EXCERSISE_REPS + " = MAX(reps)",
            null,
            null,
            null,
            Contact.ExcersiseEntry.EXCERSISE_REPS + " DESC" // Irrelevant
);

However, this would be for all Exercises and could thus return multiple rows BUT it would be a misuse of an aggregate function (attempt apply the function on a per row basis as opposed to on a per group basis (all rows are the group as no GROUP BY has been specified)).

  • You'd have to use a subquery e.g. SELECT * FROM reps WHERE reps = (SELECT MAX(reps) FROM reps)

Upvotes: 1

Related Questions