access返回行号,使用Microsoft Access查询结果中的行号

I always use this query in sql server to get Row number in a table:

SELECT *

FROM (SELECT *,

Row_number()

OVER(

ORDER BY [myidentitycolumn]) RowID

FROM mytable) sub

WHERE rowid = 15

Now I am working in Access 2010 and this seems to be not working. Is there any replacement for this query in Access?

解决方案

MS-Access doesn't support ROW_NUMBER(). Use TOP 1:

SELECT TOP 1 *

FROM [MyTable]

ORDER BY [MyIdentityCOlumn]

If you need the 15th row - MS-Access has no simple, built-in, way to do this. You can simulate the rownumber by using reverse nested ordering to get this:

SELECT TOP 1 *

FROM (

SELECT TOP 15 *

FROM [MyTable]

ORDER BY [MyIdentityColumn] ) t

ORDER BY [MyIdentityColumn] DESC