MySQL - Counting rows in preparation for greatest-n-per-group not working?

Question

Referring to SO and other sites have given me examples of how to use MySQL to create a 'greatest-n-per-group' query.

My variant on this would be to have a query that returns the first 3 rows of each category. As the basis for this I need to sort my data into a usable sequence and that is where my problems start. Running just the sequence query with row numbering shows that changes in category are mostly ignored. I should have 35 categories returning rows but only 5 do so.

My query:

set @c:=0;
set @a:=0;
SELECT IF(@c = tdg, @a:=@a+1, @a:=1) AS rownum,   (@c:=tdg) ,
julian_day, sequence, complete, year, tdg
FROM tsd WHERE complete = 0 
order by tdg, year, julian_day, sequence

Do I have a syntax mistake with this query?

Answer 1

OK, here's my comment as an answer:

The problem might be that your IF() assumes that all the rows that have common value in tdg should be sequentially in the table (i.e. no other rows in-between). ORDER is not done before the SELECT is done, but after all the rows have been fetched -> @a gets reset. Also, your reference page says you need the dummy column, so read those pages carefully.