By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
 429,374 Members | 1,768 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,374 IT Pros & Developers. It's quick & easy.

# compare rows. do something when value changes in a particular column

 P: 3 I want to compare the values within a column and aggregate (count) at each change in value. so for example column "A" has a series of numbers like (1,1,1,2,1,5,2,2,5) the aggregation should be the counts 3,1,1,1,2,1. 3 of 1, followed by 1 of value 2, followed by 1 value 1... somebody suggested duplicating the target column and shifting in down by one row and then comparing the values between the original column and the new column but I couldn't perform what he suggested. can anyone help? Feb 16 '12 #1
Share this Question
1 Reply

 Expert 100+ P: 1,035 Expand|Select|Wrap|Line Numbers mysql> select i,j from test21; +---+------+ | i | j    | +---+------+ | 1 |    1 | | 2 |    1 | | 3 |    1 | | 4 |    2 | | 5 |    1 | | 6 |    5 | | 7 |    2 | | 8 |    2 | | 9 |    5 | +---+------+   Expand|Select|Wrap|Line Numbers set @a:=1; select      t1.i I1,      t2.i I2,      t3.i I3,      t1.j J1,      t2.j J2,      t3.j J3,      @a:=IF(t1.j=t2.j,@a+1,1) A  from test21 t1  left join test21 t2      on t1.i=t2.i+1 left join test21 t3      on t1.i=t3.i-1 where (t1.j<>t2.j and t1.j<>t3.j) or (t1.j=t2.j and t1.j<>t3.j) or t3.i is NULL or t2.i is NULL It almost what you want in column A, only first two rows are wrong.... ;) Expand|Select|Wrap|Line Numbers +----+------+------+------+------+------+------+ | I1 | I2   | I3   | J1   | J2   | J3   | A    | +----+------+------+------+------+------+------+ |  1 | NULL |    2 |    1 | NULL |    1 |    1 | |  3 |    2 |    4 |    1 |    1 |    2 |    2 | |  4 |    3 |    5 |    2 |    1 |    1 |    1 | |  5 |    4 |    6 |    1 |    2 |    5 |    1 | |  6 |    5 |    7 |    5 |    1 |    2 |    1 | |  8 |    7 |    9 |    2 |    2 |    5 |    2 | |  9 |    8 | NULL |    5 |    2 | NULL |    1 | +----+------+------+------+------+------+------+   Mar 18 '12 #2

### Post your reply

Sign in to post your reply or Sign up for a free account.