How to Implement Sequential Row Numbering with Key Grouping in SQL?

Sequential Row Numbering with Key Grouping in SQL

SQL offers a powerful feature to sequentially enumerate rows within specific key groups. This functionality proves especially useful when you need to organize data based on distinct categories or attributes. Let's explore how this feature works across various SQL dialects.

Implementation in Different SQL Dialects

The following SQL dialects provide a solution for sequential row numbering by keyed group:

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

Sample Data and Desired Projection

Consider the following sample table with (CODE, NAME) tuples:

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

The desired projection using CODE as the grouping attribute would look like:

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

SQL Query to Accomplish Sequential Row Numbering

To achieve this sequential row numbering by key group, you can leverage the following SQL query:

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

This query will partition the table based on the CODE column, order the records within each partition by the NAME column, and then subtract 1 from the resulting row number to obtain the desired sequential numbering starting from 0 for each key group.

The above is the detailed content of How to Implement Sequential Row Numbering with Key Grouping in SQL?. For more information, please follow other related articles on the PHP Chinese website!