MySQL - Counting rows in preparation for greatest-n-per-group not working?
- by John M
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?
