使用Microsoft Access的查询结果中的行号 [英] Row numbers in query result using Microsoft Access

问题描述

我总是在sql server中使用此查询来获取表中的行号:

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  

现在我正在Access 2010中工作，这似乎不起作用.在Access中此查询是否可以替代?

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不支持ROW_NUMBER().使用TOP 1:

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

SELECT TOP 1 *
FROM [MyTable]
ORDER BY [MyIdentityCOlumn]

如果需要第15行-MS-Access没有简单的内置方法来执行此操作.您可以使用反向嵌套顺序来模拟行号:

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

这篇关于使用Microsoft Access的查询结果中的行号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！