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]:

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

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:

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 Replicate SQL's 'ROW_NUMBER() OVER (PARTITION BY ...)' in MS Access?. For more information, please follow other related articles on the PHP Chinese website!