How to get Max value of one column based on another column value in sqlite for Android


December 2018


329 time


Sorry I don't know much about SQL syntax. Basically, I have the following columns in my table:

x      0         23
x      1         34
x      0         24
x      1         35
x      1         33

I want to get the max DRAW NUMBER based on the GAME column value. For example, if I select GAME 1, I want to get a DRAW NUMBER of 35 (i.e., highest draw number for game 1 is 35)

The best I have come up with is the following:

String table = "xxx", col = "DRAW NUMBER", game_col = "GAME";    
SQLiteDatabase db = this.getReadableDatabase();

Cursor c =  db.query(table, null, col+"=(SELECT MAX("+col+") FROM " + table + " GROUP BY " + game_col + ")", null, null, null, null);


Also I would like to get the count of a specific game based on the GAME value. This is what I have so far:

int game_val = 1, row_count;
String table = "xxx", game_col = "GAME";
SQLiteDatabase db = this.getReadableDatabase();  

Cursor c = db.rawQuery("SELECT COUNT(*) FROM " + table + " WHERE " + game_col + " = " + game_val + "", null); 
row_count = c.getCount();

Thank you in advance!

2 answers


You can use rawQuery

For specific game, just replace the 1 with a variable (edited: removed group by, not needed in this one, thanks Bae)

Cursor c = db.rawQuery("SELECT max(DRAW NUMBER) FROM xxx WHERE GAME = 1", null);

For list of all max numbers for all games

Cursor c = db.rawQuery("SELECT GAME, max(DRAW NUMBER) FROM xxx GROUP BY GAME", null);

You only need this query

Cursor cursor = db.rawQuery("SELECT MAX(DRAW_NUMBER) FROM your_table_here WHERE GAME = 1, null);

For list you can use the query that Hrusilov told you.