SQL variable assignment and comparison logic

Question

I am looking into the following mysql:

select num, @record, 
case 
    when @record = num then @count:=@count+1
    when @record <> @record:=num then @count:=1 end as n
from 
    Logs ,(select @count:=0,@record:=(SELECT num from Logs limit 0,1)) r

Where Logs table is like:

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

And the outputs of the query is like:

 |num | @record | n   |
 ----------------------
 | 1  |     "1" | 1.0 |
 | 1  |     "1" | 2.0 | 
 | 1  |     "1" | 3.0 |
 | 2  |     "1" | 1.0 |
 | 1  |     "2" | 1.0 | 
 | 2  |     "1" | 1.0 | 
 | 2  |     "2" | 2.0 |

For the second and third row, I have hard time understand how is derived. For example, in row_1 (Id = 1), @record = Num, why n = 1 not 2? In row_3, @record = Num, why n = 3 not 2?

Is there only one @record global variable? or each num has its own @record variable?

Could anyone please help me to understand the @sql variable logic? Thanks!

slaakso · Accepted Answer · 2019-11-28 20:37:06Z

1

MySQL evaluates the JOIN first i.e @count=1 and @record=1 before any rows are processed. First row's num is the same as the @record so n gets value of 1 (@count+1).

Second and third rows follow the same logic resulting values 2 and 3.

Fourth row resets the @count and puts new num into @record

and so on...

answered Nov 28, 2019 at 20:37

slaakso

9,1452 gold badges19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

PeterHe · Accepted Answer · 2019-11-28 20:37:32Z

0

The value of column n comes from @count, not @record. @record is used to assign reset the @count to 1 when num is changed

answered Nov 28, 2019 at 20:37

PeterHe

2,7861 gold badge11 silver badges7 bronze badges

Collectives™ on Stack Overflow

SQL variable assignment and comparison logic

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related