Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

syntax error when using row_number in sqlite3

Tags:

sql

sqlite

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: this is my table and it named demo

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.

like image 923
nail fei Avatar asked May 14 '18 14:05

nail fei


2 Answers

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

like image 137
max taldykin Avatar answered Sep 28 '22 21:09

max taldykin


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

like image 42
Gordon Linoff Avatar answered Sep 28 '22 21:09

Gordon Linoff