syntax error when using row_number in sqlite3

Question

I want to remove duplicate through row_number function based on How to get get Unique Records based on multiple columns from a table . But Was blocked by a syntax error. My use case as below:

and my sql as below:

select demo.*, 
      row_number() over (partition by id order by creator desc) as rn 
from demo

but it tell me:

near "(": syntax error:

I dont know what happend and I do some search such as How to use ROW_NUMBER in sqlite . Unfortunately, I still cant figure out what mistake I make. Anything will be appreciate.

Answer 1

SQLite supports window functions since version 3.25, so the original query should work now.

Answer 2

Use subquery instead:

select d1.* 
from demo d1
where primarykey = (select d2.primarykey
                    from demo d2
                    where d2.id = d1.id
                    order by d2.creator desc
                    LIMIT 1);

Answer 3

As mentioned in the comments, SQLite does not support row_number() or other window functions. You can use a correlated subquery:

select d.*
from demo d
where d.creator = (select max(d2.creator) from demo d2 where d2.id = d.id);

With an index on demo(id, creator) this often has better performance even in databases that do support row_number().