Sub group id

[duncan121]

Hi all,

 

I am trying to add a grouping id when I select the table but a loss how to do it.

 

Any ideas?

 

Thanks so much

 

 

New id Pupil name 1 John Smith 1 John Smith 1 John Smith 1 John Smith 2 Jenny Wills 2 Jenny Wills 2 Jenny Wills 3 Mark Long 3 Mark Long 3 Mark Long

 

 

[Barand]

1/10 for formatting

 

What results are you hoping to get from that data?

New id  Pupil name 
1       John Smith 
1       John Smith 
1       John Smith 
1       John Smith 
2       Jenny Wills 
2       Jenny Wills 
2       Jenny Wills 
3       Mark Long 
3       Mark Long 
3       Mark Long

It would appear that your "new id" is a group id ???

[duncan121]

Sorry it's the new id I want to create.  That is just an example of what I want to acheive.

 

Thanks

[Barand]

OK. I'll assume we're starting with something like this:

mysql> select * from duncan;
+----+-------------+--------+
| id | pupil_name  | new_id |
+----+-------------+--------+
|  1 | John Smith  |   NULL |
|  2 | Jenny Wills |   NULL |
|  3 | Jenny Wills |   NULL |
|  4 | John Smith  |   NULL |
|  5 | Jenny Wills |   NULL |
|  6 | Mark Long   |   NULL |
|  7 | John Smith  |   NULL |
|  8 | Mark Long   |   NULL |
|  9 | John Smith  |   NULL |
+----+-------------+--------+

Then

UPDATE duncan
JOIN
    (
    SELECT 
        @grp := IF(pupil_name=@prev, @grp, @grp+1) as groupid,
        @prev:= pupil_name as pupil_name
        FROM duncan
            JOIN(SELECT @prev:='', @grp:=0) as init
        ORDER BY pupil_name
    ) as grpcalc USING (pupil_name)
SET duncan.new_id = grpcalc.groupid

Results in

mysql> select * from duncan order by pupil_name;
+----+-------------+--------+
| id | pupil_name  | new_id |
+----+-------------+--------+
|  2 | Jenny Wills |      1 |
|  3 | Jenny Wills |      1 |
|  5 | Jenny Wills |      1 |
|  1 | John Smith  |      2 |
|  4 | John Smith  |      2 |
|  7 | John Smith  |      2 |
|  9 | John Smith  |      2 |
|  6 | Mark Long   |      3 |
|  8 | Mark Long   |      3 |
+----+-------------+--------+

[duncan121]

Excellent

 

Thanks so much for your help.