Shirish Herwade Shirish Herwade - 1 month ago 25
Android Question

Misuse of aggregate function MAX()

I want latest not null value of column

ignition_status
in Alert table.

I have column
unix_time
which is time in Unix Time Stamp, so max the
unix_time
column value, latest is the entry.

Below is my code

cursor = dbUtilsObj.query(Alert.TABLE_NAME, new String[] { alertType_COLUMN }, " MAX(" + Alert.Columns.KEY_ALERT_UNIX_TIME
+ ")" + AND + Alert.Columns.KEY_MACHINE_TELE_DEVICE_NO + EQUALS + AND + alertType_COLUMN + IS_NOT_NULL,
new String[] { String.valueOf(teleDevieNo) }, null, null, Alert.Columns.KEY_ALERT_UNIX_TIME);


getting error

(1) misuse of aggregate function MAX()
: android.database.sqlite.SQLiteException: misuse of aggregate function MAX() (code 1): , while compiling: SELECT ignition_status FROM alert WHERE MAX(unix_time) and tele_device_no = ? and ignition_status IS NOT NULL ORDER BY unix_time
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1113)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:686)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1420)
at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1267)
at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1138)
at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1306)
at com.jd.database.DBUtils.query(DBUtils.java:473)
at com.jd.database.AlertDBUtils.getLatestNotNullValueForType_COLUMN_ByTeleDeviceNo(AlertDBUtils.java:161)
at com.jd.sms.SMSIntentService.parseE1_VehicleMovemenEvent(SMSIntentService.java:158)
at com.jd.sms.SMSIntentService.parseMsgAndInputInDb(SMSIntentService.java:122)
at com.jd.sms.SMSIntentService.processMessage(SMSIntentService.java:97)
at com.jd.sms.SMSIntentService.procassRequest(SMSIntentService.java:71)
at com.jd.sms.SMSIntentService.onHandleIntent(SMSIntentService.java:39)
at android.app.IntentService$ServiceHandler.handleMessage(IntentService.java:65)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loop(Looper.java:176)
at android.os.HandlerThread.run(HandlerThread.java:60)
android.database.sqlite.SQLiteException: misuse of aggregate function MAX() (code 1): , while compiling: SELECT ignition_status FROM alert WHERE MAX(unix_time) and tele_device_no = ? and ignition_status IS NOT NULL ORDER BY unix_time
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1113)


In short I'm trying the query
SELECT ignition_status FROM alert WHERE MAX(unix_time) and tele_device_no = ? and ignition_status IS NOT NULL ORDER BY unix_time


So what should go to
dbUtilsObj.query()
method above?
At least please tell me the correct raw query.

Answer

You cannot use an aggregate function like MAX() like this in the selection.

Instead consider the following:

  • ORDER BY unixtime DESC to sort the results matching your selection with the newest first.

  • LIMIT 1 to only return the first result i.e. the newest.

Comments