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
Bytes IT Community
+ 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
Share on Google+
1 Reply


Expert 100+
P: 1,035
Expand|Select|Wrap|Line Numbers
  1. mysql> select i,j from test21;
  2. +---+------+
  3. | i | j    |
  4. +---+------+
  5. | 1 |    1 |
  6. | 2 |    1 |
  7. | 3 |    1 |
  8. | 4 |    2 |
  9. | 5 |    1 |
  10. | 6 |    5 |
  11. | 7 |    2 |
  12. | 8 |    2 |
  13. | 9 |    5 |
  14. +---+------+
  15.  
Expand|Select|Wrap|Line Numbers
  1. set @a:=1;
  2. select 
  3.     t1.i I1, 
  4.     t2.i I2, 
  5.     t3.i I3, 
  6.     t1.j J1, 
  7.     t2.j J2, 
  8.     t3.j J3, 
  9.     @a:=IF(t1.j=t2.j,@a+1,1) A 
  10. from test21 t1 
  11. left join test21 t2 
  12.     on t1.i=t2.i+1
  13. left join test21 t3 
  14.     on t1.i=t3.i-1
  15. 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
  1. +----+------+------+------+------+------+------+
  2. | I1 | I2   | I3   | J1   | J2   | J3   | A    |
  3. +----+------+------+------+------+------+------+
  4. |  1 | NULL |    2 |    1 | NULL |    1 |    1 |
  5. |  3 |    2 |    4 |    1 |    1 |    2 |    2 |
  6. |  4 |    3 |    5 |    2 |    1 |    1 |    1 |
  7. |  5 |    4 |    6 |    1 |    2 |    5 |    1 |
  8. |  6 |    5 |    7 |    5 |    1 |    2 |    1 |
  9. |  8 |    7 |    9 |    2 |    2 |    5 |    2 |
  10. |  9 |    8 | NULL |    5 |    2 | NULL |    1 |
  11. +----+------+------+------+------+------+------+
  12.  
Mar 18 '12 #2

Post your reply

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