How to Assign Sequential Row Numbers by Key Group in SQL?

Sequential Row Numbering by Key Group in SQL

In SQL, the ROW_NUMBER() function assigns sequential numbers to rows within a result set. To assign sequential numbers by key group, use the PARTITION BY clause to group the data by the desired key(s).

Here's an example for the given table and desired output:

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

Output:

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

This query assigns sequential row numbers (C_NO) for each key group (CODE), starting from 0, in ascending order of NAME within each group.

Supported Databases:

The ROW_NUMBER() function with PARTITION BY is supported in the following databases:

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

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