oracle rownum transfer to t-sql row_number() over with error

the simplified origin pl_sql is

select t.*, staff_no||'-'||rownum as pk 
from (select * from hrmmgr.posting where type not in ('X','C') order by staff_no, postingdate) t;

I try to rewrite it to run in sql server as

select t.*, staff_no + '-' + t.rownum as pk
from (select row_number() over (order by staff_no, postingdate) as rownum, * 
from posting where type not in ('X','C') order by staff_no, postingdate) t

but error

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

is returned.

If I try to add OFFSET as shown in the error message as follow:

select t.*, staff_no + '-' + t.rownum as pk
from (select row_number() over (order by staff_no, postingdate OFFSET 0 ROWS) as rownum, * 
from posting where type not in ('X','C') order by staff_no, postingdate) t

then the following error is shown.

Incorrect syntax near 'ROWS'.

So how to rewrite my plsql to run in sql server?

Solution

The ORDER BY clause in the subquery appears to be the problem, and SQL Server is telling you that ORDER BY cannot appear in a subquery without also using something like TOP. However, it should not even be necessary here, because your call to ROW_NUMBER already specifies an ORDER BY clause to be used when assigning row numbers. Try this version:

SELECT t.*, staff_no + '-' + t.rownum AS pk
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY staff_no, postingdate) AS rownum, * 
    FROM posting
    WHERE type NOT IN ('X', 'C')
) t;