CODEWITHSUNDEEP
mysqlsequence
Tanmoy Ghosh
Tanmoy Ghosh

Reputation: 21

Generating Sequence for Each Group in MySQL

How to generate this sequence for MySQL?
(N.B. :- for each unique pair the sequence is generated afresh)

1,1 -- 1
1,1 -- 2
1,1 -- 3
1,2 -- 1
1,2 -- 2
1,3 -- 1
1,4 -- 1
1,4 -- 2
1,4 -- 3

Upvotes: 2

Views: 2833

Answers (2)

Prithviraj Yemukapati
Prithviraj Yemukapati

Reputation: 11

create a table:-

create table dummy(id varchar(100));

insert values in a table :-

insert into dummy (id) values('1,1');    
insert into dummy (id) values('1,1');    
insert into dummy (id) values('1,1');    
insert into dummy (id) values('1,2');    
insert into dummy (id) values('1,2');    
insert into dummy (id) values('1,3');    
insert into dummy (id) values('1,4');    
insert into dummy (id) values('1,4');    
insert into dummy (id) values('1,4');

run the below query:- 

select id,@aaaa:=ifnull(seq,@aaaa:=@aaaa+1)as seq from(select y.id,b,seq from (select 
id,@aaa:=@aaa+1 as b from dummy cross join (select @aaa:=0)a) as y  left join 
(select id,a,@aa:=@aa as seq from  (select * from (select id,@a:=@a+1 as a from dummy 
cross join (select @a:=0)a)as b group by id)a cross join (select @aa:=1)as d )
 as x on x.a=y.b) as z cross join  (select @aaaa:=0)a;

Upvotes: 1

Frederick Zhang
Frederick Zhang

Reputation: 3683

You need the row_number function in SQL Server for MySQL

SELECT
  @row_number := CASE
    WHEN @conc_no = conc THEN
    @row_number + 1
    ELSE
    1
    END AS num,
  @conc_no := mydata.conc AS conc
FROM
  (
    SELECT
      CONCAT(num1, ",", num2) AS conc
    FROM
      table1
    ORDER BY
      num1 ASC, num2 ASC
    ) mydata

Fiddle: http://sqlfiddle.com/#!9/ad4a0/3/0

Upvotes: 4

Related Questions