How to Simulate ROW_NUMBER() Functionality in Microsoft Access?

Achieving Row Number Functionality in MS Access

The "ROW_NUMBER OVER (PARTITION BY)" function, commonly used in SQL to assign sequential numbers within partitions of a result set, can be emulated in Microsoft Access using self-joins and aggregation.

Consider the following data set:

DOC_TYPE    Ino
3a  1800xxc1
3b  1810xxc2
3c  1700xxc3
3a  1700xxc4
3a  1800xxc5
3a  1800xxc6
3b  1800xxc7

To replicate the "ROW_NUMBER" behavior, we can use a query like this:

SELECT 
    t1.DOC_TYPE,
    t1.Ino,
    COUNT(*) AS RowNum
FROM 
    YourTable AS t1
    INNER JOIN
    YourTable AS t2
        ON t2.DOC_TYPE = t1.DOC_TYPE
            AND t2.Ino <= t1.Ino
GROUP BY
    t1.DOC_TYPE,
    t1.Ino
ORDER BY 1, 3

This query self-joins the table on the DOC_TYPE column and a less-than-or-equal comparison of the Ino values. It then aggregates the rows by DOC_TYPE and Ino, counting the occurrences of each unique DOC_TYPE, Ino pair. The resulting count, assigned to the RowNum alias, represents the sequential row number.

Applying this query to the sample data will produce the following result:

DOC_TYPE    Ino RowNum
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 to Simulate ROW_NUMBER() Functionality in Microsoft Access?. For more information, please follow other related articles on the PHP Chinese website!