I am having trouble trying to use the ROW_NUMBER function in ACCESS SQL.
The server on my computer is Microsoft SQL Server 2005 Compact Edition [ENU].
I have tried two different pieces of SQL my own as follows:-
SELECT A.GAMES, B.COUNTRY, A.GOLD, A.SILVER, A.BRONZE, ((A.GOLD* 1000000)+(A.SILVER* 1000)+A.BRONZE) AS CALCS, ROW_NUMBER()
OVER (PARTITION BY A.GAMES ORDER BY CALCS desc) AS RowNumber
FROM COUNTRYYEAR AS A, COUNTRYNAME AS B, GAMESLIST AS C
WHERE (((A.GAMES)=[C].[GAMES]) AND ((A.CODE)=[B].[CODE]) AND ((C.TYPE)='S'))
ORDER BY A.GAMES
The other has been copied from the internet as a so called working SQL statement:-
,ROW_NUMBER() over(PARTITION BY FamilyID,
CONVERT(NVARCHAR(25), DateOfBirth, 111)
ORDER BY DateOfBirth ASC) TwinCode
ORDER BY PersonID
In both cases, I get "syntax error (missing operator) in query expression ROW_NUMBER(), etc'
Is there a solution to this or am I wasting my time?