MySQL Row Numbering

MySQL is an awesome database tool however it's missing something pretty crucial to help us developers make epically-super-powerful queries. Row numbering.

I was trying to LIMIT a GROUP BY clause so that I'd only get 10 rows for each user as one result set. Unfortunately that's impossible without a function such as SQL's ROW_NUMBER().

Here is the solution to replicate this behaviour. Power.

{% codeblock lang:sql %} SELECT *
FROM (
SELECT id, agentid, message, timestamp, @rn := CASE WHEN @prev = agentid THEN @rn + 1 ELSE 1 END AS rn, @prev := agentid FROM _log, (SELECT @prev := 0, @rn := 0) AS vars ORDER BY agentid, timestamp DESC ) _l WHERE rn <= 10
ORDER BY agent
id, timestamp DESC
{% endcodeblock %}


comments powered by Disqus