How to Number Rows Sequentially by Group in SQL?

Numbering Rows Sequentially by Group Key in SQL

In SQL, you can sequentially number rows within a key group using the ROW_NUMBER() function. This function assigns a unique number to each row within a partition, which is defined by the PARTITION BY clause.

Syntax:

SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY key_column ORDER BY order_column) - 1 AS row_number
FROM
    table_name

Examples:

Consider the table provided:

CODE	NAME
A	Apple
A	Angel
A	Arizona
B	Bravo
C	Charlie
C	Cat
D	Dog
D	Data
D	Down
D	Doppler

To number the rows sequentially within the CODE group, use the following query:

SELECT
    CODE,
    ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY NAME) - 1 AS C_NO,
    NAME
FROM
    MyTable

This will produce the desired result:

CODE	C_NO	NAME
A	0	Apple
A	1	Angel
A	2	Arizona
B	0	Bravo
C	1	Charlie
C	0	Cat
D	0	Dog
D	1	Data
D	2	Down
D	3	Doppler

Compatibility:

The ROW_NUMBER() function is supported by the following databases:

• SQL Server
• Oracle
• Postgres
• Sybase
• MySQL 8.0
• MariaDB 10.2
• SQLite 3.25

This covers most commonly used SQL databases.

The above is the detailed content of How to Number Rows Sequentially by Group in SQL?. For more information, please follow other related articles on the PHP Chinese website!