duncan121 Posted November 26, 2013 Share Posted November 26, 2013 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted November 26, 2013 Share Posted November 26, 2013 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 ??? Quote Link to comment Share on other sites More sharing options...
duncan121 Posted November 26, 2013 Author Share Posted November 26, 2013 Sorry it's the new id I want to create. That is just an example of what I want to acheive. Thanks Quote Link to comment Share on other sites More sharing options...
Barand Posted November 26, 2013 Share Posted November 26, 2013 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 | +----+-------------+--------+ Quote Link to comment Share on other sites More sharing options...
duncan121 Posted November 26, 2013 Author Share Posted November 26, 2013 Excellent Thanks so much for your help. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.