How to Sequentially Number Rows within Groups in SQL?

Sequentially Numbering Rows by Keyed Group in SQL

The task of sequentially numbering rows grouped by a specific key in SQL is a common requirement in data processing. To achieve this, various SQL databases offer native functions and techniques that allow for efficient and accurate row numbering within each key group.

Implementation in Different Databases

Many popular SQL databases provide built-in functions or extensions for sequential row numbering:

• SQL Server: ROW_NUMBER() OVER (PARTITION BY ...)
• Oracle: ROW_NUMBER() OVER (PARTITION BY ...)
• Postgres: ROW_NUMBER() OVER (PARTITION BY ...)
• Sybase: ROW_NUMBER() OVER (PARTITION BY ...)
• MySQL 8.0 : ROW_NUMBER() OVER (PARTITION BY ...)
• MariaDB 10.2 : ROW_NUMBER() OVER (PARTITION BY ...)
• SQLite 3.25 : ROW_NUMBER() OVER (PARTITION BY ...)

Example Query

Consider the following example table with tuples grouped by the CODE column:

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

To sequentially number rows within each CODE group, the following query can be used:

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

Output

The query will produce the following output, with rows numbered sequentially within each CODE group:

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

Note:

The ROW_NUMBER() function starts numbering from 1 by default. To align with the desired output, the expression ROW_NUMBER() OVER (PARTITION BY ...) - 1 is used to shift the numbering from 1-based to 0-based.

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