I have this query:
SELECT @ROWNUM := CASE WHEN @PREV_CAT_ID=T1.category_id THEN @ROWNUM+1 ELSE 1 END AS RANK,
@PREV_CAT_ID:=T1.category_id,
T1.*
FROM(d.category_id
FROM some_table d,
(SELECT @ROWNUM := 0)R
ORDER BY d.category_id
}T1
I want this RANK column to increment for records with same category_id. When category_id increases, I want rank to reset to 1 and start incrementing again.
This works great when I execute my query from TOAD, but when inside my web application or when executed from phpMyAdmin the RANK is always 1, no matter what I do.
Does anyone have any idea, what the problem might be?
Thank You!