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.

0
Would love your thoughts, please comment.x
()
x