There is a column in which there are positive and negative values. Need to calculate the maximum number of CONTRACT. Those. for example: 22, 33, 56, -85, -45, 68 - here it turns out 3 in a row positive and 2 in a row negative. Help compile a SQL query

  • And what is a "contract"? Apparently there is still some sort of column with the sort order of the records? - Mike
  • And just yesterday there was a question ru.stackoverflow.com/questions/568258/… look at the subquery in the first example in the answer, there are just the same values ​​in a row numbered ... - Mike
  • The contract meant sorting by date column and time column - Alexander

1 answer 1

Using the sign() function, we get the sign of the numbers and compare it with the sign from the previous line. We number the records in a row until the sign is saved. The maximum sequence numbers for each character is what you are looking for:

 select S, max(num) from ( select @num:=if(sign(VAL)=@lp,@num+1,1) num,@lp:=sign(VAL) S from table1, (select @lp:=0, @num:=0) A order by ORDER_COLUMN ) A group by S