mysql - Counting records in sql statement fails -

i have query:

select @i:=@i+1, s.*  quiz.score s  id between @a - @l1 , @a + @l2 order points desc;

@i:=@i+1 shoult increment each row each record null.

i dont see problem. me?

like user-defined variable, @i has initial value of null @ start of session, , null + 1 yields null. null not 0.

you should initialize @i := 0 before starts counting.

you can in separate statement:

set @i:=0;  select @i:=@i+1, s.*  quiz.score s  id between @a - @l1 , @a + @l2 order points desc;

or trick people write subquery it:

select @i:=@i+1, s.*  (select @i:=0) _init join quiz.score s  id between @a - @l1 , @a + @l2 order points desc;

one final way resolve initial case default @i 0 coalesce():

select @i:=coalesce(@i,0)+1, s.*  quiz.score s  id between @a - @l1 , @a + @l2 order points desc;

the coalesce() function returns first argument not null.

Bradly