SQL Server CE - ROW_NUMBER

Question

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.

Answer 1

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;

Answer 2

SQL Server CE does not have row_number. and it also cannot do scalar sub-queries which can also be used to get rownumber.

but you can do inequality joins... counting joins.

this would give you a rownumber.

select count(r.id) as Row_Number, s.id 
from story as s
    inner join story as r
        on s.id >= r.id
group by s.id