My only question with regard to this answer lies in the following line:
WHEN @record <> @record:=Num THEN @count:=1
I understand this is to check if the previous Num from last row equal to the Num from the current row. So is @record <> @record:=Num a assignment as well? It compares the original value in @record and the new value Num in the new @record?
I think you need the order by because of your generic solution. This is the easiest medium problem, so I would think 3 joins is an acceptable solution here, which orders correctly. Can you please explain how yours work or point to the documentation.
Looks like there's a prob with the initializing the variables.
select distinct a.Num
when @key1<> Num then @count:=0
when @key1=Num then @count:=@count+1
end as rep_count,
when @key1<>Num then @key1:=Num
when @key1=Num then Num
end as key1
from Logs a, (select @count := 0, @key1 := 0) b
with a runtime of 1587ms when i use CASE statement. But looks like IF is much more faster (1390ms)