Adding numbering to not sorted query.
If your query is not sorted, adding a number to a query can be as easy as:
SELECT
ROW_NUMBER() OVER() AS num,
This will work if you will not use ORDER BY clause in your query:
If you add ORDER BY
clause, things work differently.
Result will not get sorted as we expected. The numbering is assigned to a row while the column gets put together, and before it is sorted. So the result will look as this:
Knowing the issue, we need to approach result numbering with ORDER BY statement differently.
Add numbering column to result of sorted query
Incremental variable with JOIN for the rescue:
SELECT
@c := @c + 1 AS num,
p.*
FROM
personellist p
JOIN
(SELECT @c := 0) AS notimportantbutbeedtobeset
ORDER BY
p.name_surname ASC;
@c := @c + 1 AS num
is the variable we set later with JOIN statement (SELECT @c := 0)
the alias AS notimportantbutbeedtobeset
is required to be set but not used anywhere with in the query.
NOTICE! It is required to use table aliasing if you want to use numbering column as a first column of the table.