SQLiteException: near "ORDER" while compiling

Question

I am trying to delete last inserted Item from database table, but always getting SQLiteException: near "ORDER" syntax error (code 1)

android.database.sqlite.SQLiteException: near "ORDER": syntax error (code 1): , while compiling: delete from pending where url = 'server' and mobile_id = '2331' and sent = '0' ORDER BY id DESC LIMIT 1

Using following query:

db.execSQL("delete from pending where url = 'server' and mobile_id = '" + string + "' and sent = '0' ORDER BY id DESC LIMIT 1");

@PavneetSingh I have 5 records for same mobile_id with sent status equals to 0,but I would like to delete only last inserted, otherwise its deleting all the records — Sophie, Dec 06 '16 at 08:06
put order in where like this question http://stackoverflow.com/questions/13028725/delete-order-by-in-sqlite-android — shuabing, Dec 06 '16 at 08:08

score 3 · Accepted Answer · answered Dec 06 '16 at 08:06

3

Use nested query as follows.

db.execSQL("delete from pending where TABLE_ID = (SELECT TABLE_ID from pending where url = 'server' and mobile_id = '" + string + "' and sent = '0' ORDER BY id DESC LIMIT 1"));

TABLE_ID should be unique key id of pending table.

answered Dec 06 '16 at 08:06

additionster

628
4
14

most clean and easiest solution – Sophie Dec 06 '16 at 08:40
Hello there, please have a look at this : http://stackoverflow.com/questions/41015994/sqlite-combine-value-of-two-columns – Sophie Dec 07 '16 at 11:15

score 2 · Answer 2 · answered Dec 06 '16 at 08:11

2

DELETE FROM table
WHERE ID IN
        (
        SELECT ID
        FROM
            (
                SELECT ID
                FROM table
                WHERE url = 'server' and mobile_id = '2331' and sent = '0' 
                ORDER BY id
                DESC LIMIT 1
            ) a
        )

Note the a serves as an alias for the subquery

answered Dec 06 '16 at 08:11

Nas

2,158
1
21
38

Neither the alias nor the extra subquery are necessary. – CL. Dec 06 '16 at 09:28

SQLiteException: near "ORDER" while compiling

2 Answers2