For MyISAM and BDB tables you can have an auto_increment field as a secondary part of key, e.g.
CREATE TABLE foo (
id INT AUTO_INCREMENT NOT NULL,
group_field INT NOT NULL,
PRIMARY KEY(group_field, id)
Here's what the manual says about this
In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.
Why can't you do this in InnoDB. What's the best way to emulate this in innodb if its even possible.