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 supports window functions since version 3.25, so the original query should work now.
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()
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With