Error:android.database.sqlite.SQLiteException: near "WHERE": syntax error: , while compiling:

Question

 Cursor cursor = db.rawQuery("SELECT id,no,name,lastname FROM mytable WHERE " +
                                   "id = (SELECT MIN(id) and no=0 FROM mytable ); ",null);

I am getting that error at below, Could you please help me ?

What I want is to retrieve the record that has min Id where no=0 in the mytable....

Error:android.database.sqlite.SQLiteException: near "WHERE": syntax error: , while compiling:

ravuya · Accepted Answer · 2012-04-12 02:41:17Z

3

Without knowing too many specifics about sqlite syntax, I'd guess your subquery isn't formatted quite right.

Try

SELECT id, no, name, lastname FROM mytable WHERE
       id = (SELECT min(ID) FROM mytable WHERE no = 0)

or

SELECT id, no, name, lastname FROM mytable WHERE
       id = (SELECT min(ID) FROM mytable) AND no = 0

depending on what your interpretation was (but it sounds like the first one). The first will return rows with the minimum id with also a no of 0, but the second query will return the minimum id and then use it to select for a row with a no of 0.

edited Apr 12, 2012 at 2:41

answered Apr 12, 2012 at 2:32

ravuya

8,8065 gold badges32 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Tim Over a year ago

+1 - however, you might want to add an explanation of what the difference is between the two statements to further clarify. :)

ravuya Over a year ago

Thanks, I've made that edit (which hopefully didn't confuse things further). I also just checked it out in sqlite3 on my local machine and it seems to be correct.

Co Koder Over a year ago

Thanks for your replies, I tried that one, Cursor cursor = db.rawQuery("SELECT id,no,name,lastname FROM mytable WHERE id = (SELECT min(ID) FROM mytable WHERE no = 0);",null); But still, I have an error message.....android.database.CursorIndexOutOfBoundsException: Index -1 requested, with a size of 1

Nishant Over a year ago

You should move on to first row in the cursor by calling Cursor cur = db.raqQuery("Select * from myTable",null); if (cur != null) { if (cur.moveToFirst()) { do { cur.getString(cur.getColumnIndex("Name"))); // "Name" is the field name in table } while (cur.moveToNext()); } }

Nishant Over a year ago

it is because the cursor is initially pointing to row at index -1 but actual row starts from index 0. Calling moveToFirst() will move the cursor to index 0.

|

Collectives™ on Stack Overflow

Error:android.database.sqlite.SQLiteException: near "WHERE": syntax error: , while compiling:

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related