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
– maeenul, Commented Dec 16, 2018 at 12:28

max taldykin · Accepted Answer · 2018-12-03 11:41:38Z

9

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

answered Dec 3, 2018 at 11:41

max taldykin

13k5 gold badges49 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2018-05-14 14:35:33Z

1

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

answered May 14, 2018 at 14:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Yogesh Sharma · Accepted Answer · 2018-05-14 14:49:11Z

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, 2018 at 14:49

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Collectives™ on Stack Overflow

syntax error when using row_number in sqlite3

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related