CODEWITHSUNDEEP
mysqlsql
Kalenji
Kalenji

Reputation: 407

MySQL 5.0 assign number based on different column

I have been trying to figure out how to assign the number (in my case the group number based on the value in different column). I have a table with a number and based on that number I try to assign the group number. The number is the order of the table and it can be the same for several rows. 

create table test (
    code varchar(10) primary key,
    num varchar(10) not null,
    name varchar(10) not null,
    surname varchar(10) not null);

insert into test values (1,9,'Tom', 'Smith');
insert into test values (2,9,'Adam','Blake');
insert into test values (3,15,'John','Smith');
insert into test values (4,15,'Adam','XYZ');
insert into test values (5,43,'John','Abc');
insert into test values (6,99,'Adam','Abc');
insert into test values (7,99,'John','Abc');

So the test table looks like this:

enter image description here

and the desired output looks like this where the grp value is always the consecutive number starting from 1.

enter image description here

Code for results:

create table result (
    code varchar(10) primary key,
    num varchar(10) not null,
    name varchar(10) not null,
    surname varchar(10) not null,
grp varchar(10) not null);

insert into result values (1,9,'Tom', 'Smith',1);
insert into result values (2,9,'Adam','Blake',1);
insert into result values (3,15,'John','Smith',2);
insert into result values (4,15,'Adam','XYZ',2);
insert into result values (5,43,'John','Abc',3);
insert into result values (6,99,'Adam','Abc',4);
insert into result values (7,99,'John','Abc',4);

Can this be achieved without creating any functions and variables? Are there any pseudo columns that describe it and could be used?

Upvotes: 0

Views: 130

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Use subquery :

select *, (select count(distinct t1.num) from test t1 where t1.num <= t.num) as grp
from test t;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can use a correlated subquery:

select t.*,
       (select count(distinct t2.num)
        from test t2
        where t2.num <= t.num
       ) as grp
from test t;

A more efficient method uses variables:

select t.*,
       (@grp := if(@n = t.num, @grp,
                   if(@n := t.num, @grp + 1, @grp + 1)
                  )
       ) as grp
from (select t.*
      from test t
      order by t.num
     ) t cross join
     (select @grp := 0, @n := -1) params;

Upvotes: 1

Related Questions