CODEWITHSUNDEEP
mysqlauto-increment
japem
japem

Reputation: 1111

How do I assign auto-incrementing IDs that restart for each new person in MySQL?

If I have a table like this:

PersonId   
Joe         
Joe
Joe
Joe
Frank
Frank
Frank
Frank

And want to get it to look like this:

PersonId   id
Joe        1     
Joe        2
Joe        3
Joe        4
Frank      1
Frank      2
Frank      3
Frank      4

How do I do that? Something like alter table add id int auto_increment, add primary key (id) but with more arguments to make the auto-increment reset after each new name and with no primary key because there will be duplicate values.

Upvotes: 1

Views: 53

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Try this for doing it on the fly:

select personid, 
       (@rn := if(@p = personid, @rn + 1,
                  if(@p := personid, 1, 1)
                 )
       ) as id
from your_table cross join
     (select @rn := 0, @p := 0) vars
order by personId;

This differs from Juergen's answer in two specific ways. First, there is an explicit order by because you cannot rely on the ordering of a select with no order by. Second, both variables being used are assigned in a single statement. MySQL does not guarantee the order of evaluation of statements in a select, so the only safe way to do this is with a single statement.

Upvotes: 1

CodeLikeBeaker
CodeLikeBeaker

Reputation: 21312

Here is an example of what you can do with a sample create table, and insert script to go along with it:

CREATE TABLE person(
    my_name VARCHAR(200)
);

INSERT INTO person VALUES ('Joe'), ('Joe'),('Joe'),('Joe'),('Frank'),('Frank'),('Frank'),('Frank');


SET @my_name := '';
SET @sort := 0;
SELECT 
    my_name
    , @sort := CASE WHEN @my_name = p.my_name THEN 
                @sort + 1
            ELSE 
                 1
            END AS sort_order
    , @my_name := p.my_name AS my_name
FROM 
    person p;

Upvotes: 1

Ian McLaird
Ian McLaird

Reputation: 5585

From the documentation

For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. 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.

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

Upvotes: 1

Related Questions