How to use the same function like Oracle Rownum in MS ACCESS

ms-access, rownum, sql

Solution

Access does not support that function. If your `ID` field is a numeric primary key, you can include a field expression which is the count of the number of rows with `ID` <= to the current `ID` value.

SELECT
    DCount('*', 'YourTable', 'ID <= ' & y.ID) AS NID,
    y.ID,
    y.value
FROM YourTable AS y;

You could use a correlated subquery instead of `DCount` if you prefer.

And `ID` does not actually have to be a primary key. If it has a unique constraint it is still suitable for this purpose.

And the targeted field does not absolutely have to be a number, but text data type can be more challenging.

Problem

I am encountering a problem, I had done a function that the data can be loaded by detecting scrolling position, the function was made with a SQL statement "Rownum", it only works in Oracle, but not in ACCESS. I would like to query the data and resort it ``` ID value 1 aa 3 bb ``` with Rownum we can do like this ``` NID ID value 1 1 aa 2 3 bb ``` how can I write a SQL statement with Microsoft ACCESS

Original source