Is it possible to reliably number rows in some order conforming to SQL standard?

Question

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.

Answer 1

There is a way to do that using only SQL 89 feature, but it will be horribly slow on any real world sized table:

select t1.id, 
       ... other columns ...,
       (select count(*) 
        from the_table t2 
        where t2.id <= t1.id) as row_num
from the_table t1
order by t1.id;

This assumes id is the column to sort the numbers by.

Answer 2

MariaDB 10.2.2 has a bunch of Windowing Functions. In theory they should be 'standard-compliant'.

An example: (also demonstrates a sequence number generator)

SELECT ROW_NUMBER() OVER(ORDER BY seq) AS row_num,
       seq*seq
    FROM seq_10_to_20_step_2;

+---------+---------+
| row_num | seq*seq |
+---------+---------+
|       1 |     100 |
|       2 |     144 |
|       3 |     196 |
|       4 |     256 |
|       5 |     324 |
|       6 |     400 |
+---------+---------+
6 rows in set (0.00 sec)