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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With