Hi everybody,

I've googled this question but didn't find anything relevant.

How can I create this query in MySQL?

select *, row_number() over (partition by GroupID order by DateField DESC) as RowNum from myTable

?

Thanks in advance.

while it is possible to generate those numbers (using a self-join) it is very inefficient

it's far easier to apply the numbers in your application language (php or whatever) as you loop over the query results

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:

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).

brian, that's pretty impressive

i keep forgetting mysql allows variables

nice job