There is a table from which you need to pull out the maximum number (3rd column), sorting out 1 and 2 columns. That is, column 1 and 2 is set (there may be many such records), but it is necessary precisely in which the number in the 3rd column is maximum or minimum.

cursor = database.query(TABLE_NAME_WEATHER, new String[]{CELSIUS, FAHRENHEIT}, MONTH + " = ?" + " AND " + DAYOFMONTH + " = ?" + " AND " + CELSIUS + " = MAX(" + CELSIUS + ")", new String[]{month, date}, null, null, null);

But an error pops up in this sample.

 android.database.sqlite.SQLiteException: misuse of aggregate function MAX() (code 1): , while compiling: SELECT celsius, fahrenheit FROM weatherTable WHERE month = ? AND dayofmonth = ? AND celsius = MAX(celsius)

    2 answers 2

    In SQLite, aggregate functions ( MAX() , SUM() , COUNT() etc) are not very friendly with WHERE . Therefore or so:

     SELECT celsius, fahrenheit FROM weatherTable HAVING month = ? AND dayofmonth = ? AND celsius = MAX(celsius)

    Or so:

     SELECT celsius, fahrenheit FROM weatherTable WHERE month = ? AND dayofmonth = ? AND celsius IN (SELECT MAX(celsius) FROM weatherTable)
    • I tried the second option. There is no error, but he does not find a single variant of the maximum, but has found the minimum. - Camel

    Try this:

     select max(celsius) as max_celsius, min(celsius) as min_celsius, max(fahrenheit) as max_fahrenheit, min(fahrenheit) as min_fahrenheit from weatherTable where month = ? AND dayofmonth = ?`

    and then so:

     cursor = database.rawQuery( "select \n"+ " max("+CELSIUS+") as max_"+CELSIUS+", min("+CELSIUS+") as min_"+CELSIUS+",\n"+ " max("+FAHRENHEIT+") as max_"+FAHRENHEIT+", min("+FAHRENHEIT+") as min_"+FAHRENHEIT+"\n"+ "from "+TABLE_NAME_WEATHER+"\n"+ "where "+MONTH+" = ? AND "+DAYOFMONTH+" = ?", new String[]{month, date});

    Threat why we need and Celsius and Fahrenheit? F = C * 9/5 + 32?