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.

SQLite 3.25 and above supports window functions. But can anyone suggest why System.Data.SQLite in c# return the syntax error? Is there anything to configure? — maeenul, Dec 16 '18 at 12:28

score 9 · Answer 1 · answered Dec 03 '18 at 11:41

9

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

answered Dec 03 '18 at 11:41

max taldykin

12,459
5
45
64

score 1 · Answer 2 · answered May 14 '18 at 14:35

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().

score 0 · Answer 3 · answered May 14 '18 at 14:49

0

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);

answered May 14 '18 at 14:49

Yogesh Sharma

49,870
5
26
52

syntax error when using row_number in sqlite3

3 Answers3

Linked

Related