Does Android Room Database Query support row_number?

Question

I am trying to get the following sqlite query working in my android room database, but it's complaining with a couple of errors.

@Query("with cte as (\n" +
        "  select t.exercise_name,t.band, t.reps, t.weight\n" +
        "  from ( \n" +
        "    select *,\n"+      "" +
        "      row_number() over (partition by band order by reps desc) rnreps,\n" +
        "      row_number() over (partition by band, reps order by weight desc) rnweight" +
        "    from tracked_exercises\n" +
        "  ) t  \n" +
        "  where exercise_name = :name\n" +
        ")  \n" +
        "select * from cte c\n" +
        "where not exists (\n" +
        "  select 1 from cte\n" +
        "  where band = c.band and reps > c.reps and weight >= c.weight\n" +
        ")  \n")
List<TrackedExercise> getPersonalRecords(String name);

1 just before the bracket in front of partition "<compound operator>, FROM, GROUP, LIMIT, ORDER, WHERE or comma expected, got '('"

And another when I try to compile "error: no viable alternative at input 'with cte as...."

I have managed to get the same query working on fiddle here

Is the row_number() over (partition' clause not supported by Room or am I missing something?

EDIT: Just found this Android Room + Window Functions looks like this function is only avaliable in SQLite 3.25 and higher Android only goes upto 3.19. I'm still interested if anyone has any ideas on how to create a query that does the same thing without using Windows Functions

Setback

Sqlite supports row_number(). Maybe something else not supported, you can check all used operators if they supported by SQLite – Acetone

Answer 1

I think I found a solution to my problem using 2 CTE's then joining the results

@Query("-- gets the max reps at each set of exercise_name, band, weight\n" +
        "with repsCTE as (\n" +
        "  select *,max(reps) reps\n" +
        "  from tracked_exercises\n" +
        "  where exercise_name = :name\n" +
        "  group by exercise_name, band, weight ), \n" +
        "\n" +
        "-- gets the max weight at each set of exercise_name, band, reps\n" +
        "weightCTE as (\n" +
        "  select *,max(weight) weight\n" +
        "  from tracked_exercises\n" +
        "  where exercise_name = :name\n" +
        "  group by exercise_name, band, reps ) \n" +
        "\n" +
        "select * \n" +
        "from repsCTE r join weightCTE w\n" +
        "on r.id = w.id\n" +
        "order by band,reps" )
List<TrackedExercise> getPersonalRecords(String name);

Fiddle

Setback