How might sub-groups have a generated increment column added in an sql query?

Question

First, this is similar to the simpler stack-o question here, With MySQL, how can I generate a column containing the record index in a table?, where a general incremented column was added to a query.

But, in this case I'm attempting to increment over individual sub-groups.

For example, consider the following table:

name    sub-group   note
john    1           yes
doe     1           no
bill    1           maybe
greg    2           so
dan     2           blue
jim     3           white

What query would output a table with an additional column incremented per sub-group, like the following:

name    sub-group   note    increment
john    1           yes     1
doe     1           no      2
bill    1           maybe   3
greg    2           so      1
dan     2           blue    2
jim     3           white   1

Is this possible?

Answer 1

I have solved it, thanks to help from a most excellent blog post here: http://www.xaprb.com/blog/2006/12/15/advanced-mysql-user-variable-techniques/

The solution is non-trivial, requiring variables and some advanced knowledge of how mysql orders its query operations, but it appears to be fairly performant. One of the keys is that variable assignments can be hidden within function calls!

Essentially, the following query solves the problem:

SET @num := 0, @type := '';

SELECT name, subgroup, @num AS increment
FROM table_name
WHERE 0 <= GREATEST(
   @num := IF(@type = subgroup, @num + 1, 1),
   LEAST(0, LENGTH(@type := subgroup)))

The functions GREATEST, LEAST, and LENGTH are just there as containers for variable assignments. As you can see, those functions are essentially doing nothing to affect the output of the query.

However, I also found that I had "subgroup" values in my table that were not consecutive. For example:

+------+----------+
| name | subgroup |
+------+----------+
| john | 1        |
| doe  | 1        |
| jim  | 1        |
| greg | 2        |
| boe  | 2        |
| amos | 3        |
| ben  | 1        |
| gary | 2        |
+------+----------+

Resulted in an output table like so:

+------+----------+-----------+
| name | subgroup | increment |
+------+----------+-----------+
| john | 1        |         1 |
| doe  | 1        |         2 |
| jim  | 1        |         3 |
| greg | 2        |         1 |
| boe  | 2        |         2 |
| amos | 3        |         1 |
| ben  | 1        |         1 |
| gary | 2        |         1 |
+------+----------+-----------+

Tacking an ORDER BY clause onto the end of the query did not work because of the execution order, and hiding the variable assignments in the ORDER BY clause came closer but had its own issues, so here is the final query that I used:

SET @num := 0, @type := '';

SELECT name, subgroup, @num AS increment
FROM (SELECT * FROM table_name ORDER BY subgroup) AS table_name2
WHERE 0 <= GREATEST(
   @num := IF(@type = subgroup, @num + 1, 1),
   LEAST(0, LENGTH(@type := subgroup)))

Resulting in the following output:

+------+----------+-----------+
| name | subgroup | increment |
+------+----------+-----------+
| john | 1        |         1 |
| doe  | 1        |         2 |
| jim  | 1        |         3 |
| ben  | 1        |         4 |
| greg | 2        |         1 |
| boe  | 2        |         2 |
| gary | 2        |         3 |
| amos | 3        |         1 |
+------+----------+-----------+

Yay!

Answer 2

What I would do is when inserting the data into the database, if you are using PHP, is query the database, search for the subgroup, if it exists, check for the latest increment, then add 1 and post to the DB. If the subgroup does not exist, insert into the db with the subgroup 4 and increment 1. If you need more info, let me know.