How to Assign Sequential Row Numbers Within Partitioned Groups in SQL?

Sequentially Numbering Rows Within Partitioned Groups in SQL

Question:

How can you assign sequential row numbers within each key group in an SQL table using a specific attribute as the partitioning factor?

Partitioning by Key Group:

The goal is to increment a row number sequentially for each unique value in a designated key column or combination of columns. For instance, if the table contains tuples of (CODE, NAME), you would want to assign sequential numbers to rows with the same CODE value, as illustrated below:

Original Table:

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

Desired Result with Sequential Row Numbers Partitioned by CODE:

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

Implementation:

Multiple SQL dialects support this functionality through the ROW_NUMBER() OVER () analytic function:

SQL Server:

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

Oracle:

SELECT
    CODE,
    RANK() OVER (PARTITION BY CODE ORDER BY NAME) - 1 As C_NO,
    NAME
FROM
    MyTable

Postgres:

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

Sybase:

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

MySQL 8.0 :

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

MariaDB 10.2 :

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

SQLite 3.25 :

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

By using these queries, you can effectively assign sequential row numbers within each key group in your SQL table.

The above is the detailed content of How to Assign Sequential Row Numbers Within Partitioned Groups in SQL?. For more information, please follow other related articles on the PHP Chinese website!