Misuse of aggregate function MAX()

Question

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 aggregate functions in the where-clause... – germi Mar 17 '15 at 10:11 — germi, Mar 17 '15 at 10:11
@germi thanks, it helped – Shirish Herwade Mar 17 '15 at 12:40 — Shirish Herwade, Mar 17 '15 at 12:40

score 8 · Accepted Answer · answered Mar 17 '15 at 11:05

8

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.

answered Mar 17 '15 at 11:05

laalto

137,703
64
254
280

Currently your answer have removed my error(exception), so thanks & upvote... :) But I will check if it works logically as I intend, and if yes, then will accept this also. – Shirish Herwade Mar 17 '15 at 12:39

Prashant Sawant · Answer 2 · 2016-10-22T15:55:59.660

1

Using MAX() fuction in WHERE CLUASE is surely possible and I personally tested it.like below

SELECT ignition_status FROM alert
where 
unix_time=(Select MAX(unix_time) from alert)
and tele_device_no = ?  and ignition_status IS NOT NULL  ORDER BY unix_time

edited Oct 22 '16 at 15:55

answered Oct 18 '16 at 16:30

Prashant Sawant

31
7

Misuse of aggregate function MAX()

2 Answers2