For example, if I have a table with single column numbers
and I want to 1) sort them in asc/desc order 2) (re) number them from 1 to n, using that order (so I can say that this number is ith
biggest/smallest).
If the DB doesn't have row_number function (MySQL, for example), how would one do that?
If I understood correctly, the order in which SELECT return the rows in unspecified and could be inconsistent.
And something like this (proposed in many answers to the similar question)
SET @num = 0;
SELECT number, @num := @num + 1 as num FROM ... ORDER BY number
Is wrong, since it seems that there is no guarantee regarding the time when ORDER BY would be evaluated. I.e. it can sort the numbers first, and then number them using @num
, or it could do the opposite and sort after all the rows were numbered. Am I correct?
And using inner selects won't help, since it's not guaranteed that the order will be preserved.