How Can I Simulate ROW_NUMBER() OVER (PARTITION BY) Functionality in MS Access?

Achieving Row Numbering in MS Access

The "Row Number Over Partition By" function, commonly found in databases like SQL Server, allows for the assignment of sequential integer values to rows within a specified partition of a result set. While MS Access does not natively support this function, it is possible to achieve a similar result.

Self-Join Method

One approach to emulating "Row Number Over Partition By" in Access is through an unequal self-join. Consider the following table:

Ino	Type	DOC
1	1800xxc1	3a
2	1810xxc2	3b
3	1700xxc3	3c
4	1700xxc4	3a
5	1800xxc5	3a
6	1800xxc6	3a
7	1800xxc7	3b

The following query uses a self-join to calculate row numbers within a partition based on the "DOC" field:

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 returns the following result:

DOC	Type	Ino Seq
3a	1800xxc1	1
3a	1700xxc4	2
3a	1800xxc5	3
3a	1800xxc6	4
3b	1810xxc2	1
3b	1800xxc7	2
3c	1700xxc3	1

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