Sequentially number rows by keyed group in SQL?

sql sequential
Jé Queue picture Jé Queue · Mar 28, 2011 · Viewed 31.5k times · Source

Is there a way in SQL to sequentially add a row number by key group?

Assume a table with arbitrary (CODE,NAME) tuples. Example table:

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

Desired projection using CODE as the grouping attribute:

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

Thanks,

Answer

gbn picture gbn · Mar 28, 2011

MySQL doesn't AFAIK. This covers most bases..

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

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More