SQLite - Achieve equivalent row_number and window outcome in SQLite pre 3.25.0

Question

SQLite version shipped with Android, at most is 3.19 - https://developer.android.com/reference/android/database/sqlite/package-summary

However, recently, I tend to perform row_number() and window function. However, it is only supported since 3.25.0 - https://www.sqlite.org/windowfunctions.html

My intention is to achieve the following using row_number() and window function - https://stackoverflow.com/a/60955480/72437

with cte as (
  select *, row_number() over win - 1 as rn from plain_note

  window win as (order by 
    title desc,
    case when type = 0 then body else searched_string end desc
  )    
)
update plain_note set "order" = (select rn from cte where "order" = plain_note."order");

However, such functions are not available in Android. I was wondering, is there any way to achieve same outcome, without using row_number and window?

Answer 1

It is not elegant but it works:

update plain_note 
set "order" = (
  select count(*) 
  from plain_note p 
  where p.title > plain_note.title 
  or ( 
    p.title = plain_note.title 
    and 
    case when p.type = 0 then p.body else p.searched_string end > 
    case when plain_note.type = 0 then plain_note.body else plain_note.searched_string end
  ) 
  or (
    p.title = plain_note.title 
    and 
    coalesce(case when p.type = 0 then p.body else p.searched_string end, '') = 
    coalesce(case when plain_note.type = 0 then plain_note.body else plain_note.searched_string end, '')
    and 
    p.rowid < plain_note.rowid 
  )
);