Batch number is not incremented
<pre><code>CREATE TABLE  practice_mysql.sample (  id int(11) default NULL,  Firstname varchar(45) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into sample (id,Firstname) values ('1','Anish');
insert into sample (id,Firstname) values ('2','Anish');
insert into sample (id,Firstname) values ('3','Uma');
insert into sample (id,Firstname) values ('4','Anish');
insert into sample (id,Firstname) values ('5','Uma');
insert into sample (id,Firstname) values ('6','temp');
</code></pre>

<p>**</p>

<blockquote>
  <p>and i Try this query for firstname taken first 3 letter and the i have
  using to generate auto increment</p>
</blockquote>

<p>** </p>

<pre><code>SET @ndx = 0;
SET @cur_hash = '';
SELECT id,Firstname ,CONCAT(left(Firstname ,3),IF(ndx&gt;=0,CONCAT('--00',ndx),'')) BatchNumber
FROM (SELECT id,
   @new_hash := MD5(Firstname ),
   (@ndx := IF(@new_hash=@cur_hash,@ndx+1,1)) ndx,
   @cur_hash := MD5(Firstname ),
   Firstname
FROM (SELECT id,Firstname FROM sample ORDER BY Firstname ) AA) A order by id;
</code></pre>

<p>i tried this code but the output is wrong. What is the mistake here?</p>

<pre><code>    Firstname | Batchnumber
    ---------------------

    Anish     |  Ani001
    Anish     |  Ani001
    Uma       |  Uma001
    Anish     |  Ani001
    Uma       |  Uma001
    temp      |  tem001
</code></pre>

<p>But I need this type of output:</p>

<pre><code>    Firstname | Batchnumber
    -----------------------

       Anish     |  Ani001
       Anish     |  Ani002
       Uma       |  Uma001
       Anish     |  Ani003
       Uma       |  Uma002
       temp      |  tem001
</code></pre>

<p>When the Name is inserted more than once, I have incremented the Batch number. But its not working.
I will try to find it out myself but its tough for me. can anybody solve it?</p>