I've never needed to do this myself, I came up with this just now - as such use with caution, as there may be limitations. But this may at least get you on the right path:
@i := if(@lastGroupID != GroupID, 1, $i + 1) as row_number,
@lastGroupID := GroupID,
(select @i := 0) row_number_table,
(select @lastGroupID := null) group_id_table
order by GroupID
Basically @i is a row number increment variable and @lastGroupID stores the GroupID. For each row that returns, @i is either incremented or reset if the new GroupID is different from the old one (stored in @lastGroupID from the last run). After @i is sorted out, @lastGroupID is assigned the new GroupID for the next run. It's the same logic you would use in your application code, just making the database server do the heavy lifting (the way SQL Server does).
Last edited by BrianSteffens; 07-18-11 at 03:31.
Reason: renamed @l to @lastGroupID.. @i, @l, and 1 made it hard to read :)