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.

3 Answers

2
forpas On Best Solutions

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.

0
M D On

How about this

SELECT DISTINCT file_id, pages from tbl_test GROUP BY file_id

Result

enter image description here

1
AIMIN PAN On

Strange M D's query runs. Only group key and aggregate column can be in select list, if you use group by. So pages is invalid select column.

Correct query should be

SELECT file_id, MAX(pages) from tbl_test GROUP BY file_id