SQLTeam.com | Weblogs | Forums

ROW_NUMBER() Function


#1

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?


#2

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.


#3

Thanks very much. I thought I might have been wasting my time. I'll see if I can achieve it another way.


#4

You could use a SQL Passthrough Query


#5

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