0

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.

2
  • @a_horse_with_no_name well, more or less. I want a solution that is guaranteed to be deterministic. There is no row_number() in MySQL, so I'm asking what would be a correct way to emulate it.
    – Dan M.
    Dec 17, 2016 at 18:32
  • @a_horse_with_no_name when, I guess, the answer that is guaranteed (either by the SQL standard or by MySQL documentation) to behave in the same way, even if it's in MySQL only (using mysql extensions) would be OK too.
    – Dan M.
    Dec 17, 2016 at 18:36

2 Answers 2

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.

1
  • Thanks, that'll do. My question was more theoretical than practical anyway).
    – Dan M.
    Dec 18, 2016 at 23:32
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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.