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:-
SELECT
[PersonID]
,[FamilyID]
,[FirstName]
,[LastName]
,[DateOfBirth]
,ROW_NUMBER() over(PARTITION BY FamilyID,
CONVERT(NVARCHAR(25), DateOfBirth, 111)
ORDER BY DateOfBirth ASC) TwinCode
FROM [People]
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?
I don't think the SQL variant implemented in Microsoft Access supports the ROW_NUMBER function. You can find workarounds if you search. None of the work arounds are as simple or intuitive as the ROW_NUMBER function though.
yeah, depends on what flavour of SQL you are using. AFAIK ROW_NUMBER() is only supported on Microsoft SQL Server and Oracle - and not in Access. As @jotorre_riversidedpss.org says, given that you are using Microsoft SQL Server 2005 then provided the data you are querying is stored in SQL, rather than Access, if you pass-through the query to SQL that should be fine.
I recommend that you don't convert dates to text string to sort them (nor for any actions in the WHERE clause) - string conversion will perform much slower than keeping them in their native format or, failing that, performing integer arithmetic actions on them. CONVERT style 111 is Y/M/D I think? if so that would be the normal sort order for a date anyway. If the [DateOfBirth] column includes time (seems a bit unlikely, but ...) then there are ways to do that (i.e. in MS SQL at least) which will be more efficient than a string conversion operation.
SELECT ..., ((A.GOLD* 1000000)+(A.SILVER* 1000)+A.BRONZE) AS CALCS,
ROW_NUMBER()
OVER (PARTITION BY A.GAMES ORDER BY CALCS desc)
Sadly you also cannot re-use the Alias [CALCS] from the SELECT list in the ORDER BY's OVER() clause ... you would have to repeat the formula there, or use a CTE or similar to (separately) calculate that value in order to be able to only have the formula specified only once (and reuse that value within the query 2+ times)
FROM COUNTRYYEAR AS A, COUNTRYNAME AS B, GAMESLIST AS C
WHERE (((A.GAMES)=[C].[GAMES]) AND ((A.CODE)=[B].[CODE]) AND ((C.TYPE)='S'))
Can't speak for Access, but in MS SQL that style has been superseded for some time and would be better written using JOIN syntax e.g.:
FROM COUNTRYYEAR AS A
JOIN COUNTRYNAME AS B
ON [B].[CODE] = A.CODE
JOIN GAMESLIST AS C
ON [C].[GAMES] = A.GAMES
AND C.TYPE = 'S'
The [ObjectName] square brackets are optional, except where the ObjectName is a reserve word