CODEWITHSUNDEEP
mysql
mvkperumal
mvkperumal

Reputation: 13

mysql Consecutive Column

A table has a values as below, same number can be repeated in any order. 100,102,101 are repeated value, for these value i need to generate a same number only if they are consecutive values.

100
101
102
100
100
103
104
102
102
105
106
101
101

Out put Should be

100    1
101    2
102    3
100    4
100    4
103    5
104    6
102    7
102    7
105    8
106    9
101    10
101    10

Please help for the query

Upvotes: 0

Views: 530

Answers (1)

dani herrera
dani herrera

Reputation: 51655

Here your query:

SET @t1=0;
SET @tp=-1;

select   
  @t1 := @t1 + (case when @tp=n then 0 else 1 end) as c, 
  n,
  @tp := n
from nums
order by n;

Check it at: http://sqlfiddle.com/#!2/b136e/9/2

Explanation

Do you need two variables. First one is the counter. Seccond var is to remember previus value. For each row you show counter. You increment counter when previus value is not equal than row value.

Testing:

create table nums( n int );
insert into nums values (100), (101), (101), (102);

Results:

| C |   N | @TP := N |
----------------------
| 1 | 100 |      100 |
| 2 | 101 |      101 |
| 2 | 101 |      101 |
| 3 | 102 |      102 |

Upvotes: 2

Related Questions