I don't know what is wrong with this code, it's giving me error for a day now:
select row_number() over(order by s.title) as rowNumber, s.id
from story as s
Table Definition:
id bigint
title nvarchar(100)
content ntext
Database: SQL Server Compact 4.0.8482.1
Error:
There was an error parsing the query. [ Token line number = 1,Token line offset = 22,Token in error = over ]
WHAT I DID SO FAR:
I've searched here in SO for same problem but the solution is not applicable to mine, I need row_number function so badly.
As ErikEJ already mentioned - ROW_NUMBER simply doesn't exist in SQL Server Compact Edition - not even in the newest 4.0 version.
However, if you're trying to use ROW_NUMBER to page your data, SQL Server Compact Edition 4.0 does support server-side paging through new keywords that will show up in SQL Server 2012, too - see this blog post here for all the details.
You should be able to write something like:
SELECT (columns)
FROM Story s
ORDER BY Title
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;