Same SQLite Query doesnot run in all the android version

Question

I am executing the following query in the SQLite :

SELECT * from ValueTable where SrNo = (with t as(SELECT SrNo,Value2 from ValueTable where Value2<'600' ) SELECT SrNo from t order by Value2 DESC limit 1)

The table schema is

SrNo   Value1   Value2

 1       200     450

 2       500     350

 3       100     500

So whenever I run the above query in my code, it works fine with the lollipop version. But gives error when run on the lower versions. I have no clue why. It gives the error near the 't' in the query above. What exactly am I missing ? Thanks in advance.

EDIT : Here's the error log :

     Caused by: android.database.sqlite.SQLiteException: near "t": syntax error (code 1): , while compiling: SELECT * from ValueTable where SrNo = (with t as(SELECT SrNo,Value2 from ValueTable where Value2<'600' ) SELECT SrNo from t order by Value2 DESC limit 1)
            at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
            at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:893)
            at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:504)
            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:1339)
            at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1278)
            at kunal.trial.trialsqliteinallversions.TestAdapter.getTestData(TestAdapter.java:56)
            at kunal.trial.trialsqliteinallversions.MainActivity.LoadEmployee(MainActivity.java:54)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:515)
            at android.view.View$1.onClick(View.java:3843)
            at android.view.View.performClick(View.java:4463)
            at android.view.View$PerformClick.run(View.java:18789)
            at android.os.Handler.handleCallback(Handler.java:808)
            at android.os.Handler.dispatchMessage(Handler.java:103)
            at android.os.Looper.loop(Looper.java:193)
            at android.app.ActivityThread.main(ActivityThread.java:5299)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:515)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:825)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:641)
            at dalvik.system.NativeStart.main(Native Method)

Answer 1

WITH clause support was added in SQLite 3.8.3.

API levels below 20 are using an older version of SQLite.

Refactor your query that you get the results you want with the SQL supported on all targeted API levels.