Programming

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


Comments

No comment yet.

A remark, a suggestion? Do not hesitate to express yourself below. Just be courteous and polite, please.

If this field is left blank, you will appear as Anonymous.