Menu
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,
432
To number rows in a result set, you have to use an SQL window function called ROW_NUMBER() . This function assigns a sequential integer number to each result row. However, it can also be used to number records in different ways, such as by subsets.
If you'd like to number each row in a result set, SQL provides the ROW_NUMBER() function. This function is used in a SELECT clause with other columns. After the ROW_NUMBER() clause, we call the OVER() function. If you pass in any arguments to OVER , the numbering of rows will not be sorted according to any column.
The Rank function can be used to generate a sequential number for each row or to give a rank based on specific criteria. The ranking function returns a ranking value for each row. However, based on criteria more than one row can get the same rank.
SQL – count() with Group By clause The count() function is an aggregate function use to find the count of the rows that satisfy the fixed conditions. The count() function with the GROUP BY clause is used to count the data which were grouped on a particular attribute of the table.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
