Search code examples
javaandroiddatabasesqliteandroid-sqlite

App crashes when i and ranking to my query string

"SELECT * FROM " + STUDENT_TABLES;

this code works but i want to have ranking column so i added updated it to this

  String queryString = "SELECT *,RANK () OVER" + "( " +"ORDER BY " + STUDENT_NAME_COL + " )" +  SP + " FROM " + STUDENT_TABLES ;

it gives me this error code under the opening bracket <compound,>,FROM,GROUP,ORDER.WHERE,COMMA or SEMMICOLON expected, got "(" and app crashes;

this is how it viewsenter image description here

Solution

  • Probably the version of SQLite in the device that you run the app is lower than 3.25.0 so it does not support window functions.

    You can emulate RANK() with a correlated subquery:

    String queryString = 
    "SELECT s1.*, " + 
    "(SELECT COUNT(*) + 1 FROM " + STUDENT_TABLES + " AS s2 WHERE s2." + STUDENT_NAME_COL + " < s1."  +  STUDENT_NAME_COL + ") AS SP "+
    "FROM " + STUDENT_TABLES + " AS s1";