Search code examples
javaandroidsqliteandroid-sqlite

Getting the largest value of a row among multiple entries

My question is similar to the following post: Find largest value among repeated entries in an sql table

but it is for mysql and and I cannot understand how it translates in sqlite.

I want to get the max value of pages from my table metadata with multiple file_ids and multiple entries

My table with the two columns I am interested in. 

file_id  pages
1          2
1          5
2          10
3          20 
4          12
4          1
5          4
6          5
6          14 
7          12

What I am looking for is 

file_id  pages
1          5
2          10
3          20 
4          12
5          4
6          14 
7          12

I am trying to make a query but don't know how

String[]cols = {"file_id","pages"};
String groupBy = {"pages"};

all others params are null that's as far as I can think.

What will be the query like. Please help.

I want the query in normal sqlite format rather than the raw query if possible.

Solution

  • This uses the query() method:

    String tableName = "metadata";
String[] cols = {"file_id", "max(pages) AS pages"}; 
String[] groupBy = {"file_id"};
Cursor cursor = db.query(tableName, cols, null, null, groupBy, null, null);

    Replace db with your SQLiteOpenHelper variable.