How Can I Replicate SQL's 'ROW_NUMBER() OVER (PARTITION BY ...)' in MS Access?

Implementing "Row Number Over Partition By" in MS Access

In MS Access, achieving the functionality of the "Row Number Over Partition By" function requires an alternative approach. One method is to perform an unequal self-join on the table in question.

Example:

Consider the following table, [MyData]:

To mimic the "Row Number Over Partition By" function in this case, we can use the following query:

SELECT 
    t1.DOC,
    t1.TYPE,
    COUNT(*) AS [Ino Seq]
FROM 
    MyData AS t1
    INNER JOIN
    MyData AS t2
        ON t2.DOC = t1.DOC
            AND t2.Ino <= t1.Ino
GROUP BY
    t1.DOC,
    t1.TYPE
ORDER BY 1, 3

This query retrieves the sequence number for each row within each partition defined by the [DOC] column. The output will resemble:

The above is the detailed content of How Can I Replicate SQL's 'ROW_NUMBER() OVER (PARTITION BY ...)' in MS Access?. For more information, please follow other related articles on the PHP Chinese website!