-2

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
Pemba Tamang
  • 458
  • 3
  • 16
  • `SELECT * FROM metadata WHERE pages = (SELECT MAX(pages) FROM metadata)` ? – Taseer May 03 '19 at 08:55
  • ok how about in the format that I wrote in...no problem with a raw query but I would like to know how it would be in the normal query format. Thanks for your comment – Pemba Tamang May 03 '19 at 08:56
  • Taseer I dont see any mention of `file_id` in your query – Pemba Tamang May 03 '19 at 08:57
  • `SELECT file_id, max(pages) FROM metadata GROUP BY file_id ORDER BY file_id` – Shawn May 03 '19 at 08:58
  • nope shawn.... Couldn't read row 0, col -1 from CursorWindow. Make sure the Cursor is initialized correctly before accessing data from it. I don't understand what I am missing – Pemba Tamang May 03 '19 at 09:56
  • I reset my app and read one pdf twice with different pages first time it was 10 pages and the second was 20, so I was supposed to get 20 as the max but I get the above error with your query and when I checked the cursor size the size is 2 instead of 1 as like I said I opened the same pdf twice which has the dame file_id. – Pemba Tamang May 03 '19 at 10:10

3 Answers3

2

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • I'll give it a try I think the group by does not have the parenthesis and can you please upvote my question to make it 0 idk why someone had to downvote it. – Pemba Tamang May 03 '19 at 10:17
  • Edited, groupBy needs to be an array and needs the square brackets, right. – forpas May 03 '19 at 10:18
1

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
forpas
  • 160,666
  • 10
  • 38
  • 76
AIMIN PAN
  • 1,563
  • 1
  • 9
  • 13
  • 3
    Not strange that it runs; that's just how sqlite works. From [the documentation](https://www.sqlite.org/lang.html): *Each expression in the result-set is then evaluated once for each group of rows. If the expression is an aggregate expression, it is evaluated across all rows in the group. Otherwise, it is evaluated against a single arbitrarily chosen row from within the group.* – Shawn May 03 '19 at 09:30
0

How about this

SELECT DISTINCT file_id, pages from tbl_test GROUP BY file_id

Result

enter image description here

M D
  • 47,665
  • 9
  • 93
  • 114
  • That won't work; using a non-grouped, non-aggregate value in a grouped select will pick an arbitrary row from the group for the values. OP wants the maximum page value. See my comment in the top level question for what it should be. – Shawn May 03 '19 at 09:09
  • 1
    @Shawn I am not sure what OP exactly want. He is changing statement. My SQL command produced result as per OP mentioned in section of What **I am looking for is** – M D May 03 '19 at 09:12