drkdidel.be

MariaDB / MySQL: generate a row number

There is currently no built-in method to return row numbers. The solution is to use a variable which is incremented in each row, like this:

@currentRow := @currentRow + 1 AS rowNumber

We can use a JOIN statement to initialise the variable without SET:

JOIN (SELECT @currentRow := 0) row

Another notation, replacing JOIN with a comma:

, (SELECT @currentRow := 0) row

Here is an example of an entire query:

SELECT
    title,
    text,
    @currentRow := @currentRow + 1 AS rowNumber
FROM articles
JOIN (SELECT @currentRow := 0) row

Source

Back