Can Someone explain the order of execution of ORDER BY and SELECT in MySQL when a counting variable is used in SELECT?

user_9381386 0
Viewed 36

Suppose the table is:

Data

id val
0 4
1 7
2 2
3 1
4 9

consider the query:

SET @r:=0;
SELECT val, @r:=@r+1 as row_num
  FROM Data
  ORDER BY val;

Now, I have read that ORDER BY executed after SELECT then this query should give output as below:

val row_num
1 4
2 3
4 1
7 2
9 5

BUT the query gives output as below:

val row_num
1 1
2 2
4 3
7 4
9 5

It is like ORDER BY executed first and then the SELECT executed. How the query is executing actually?

mysql
1 Answers

There are several comments to make here. First, the perceived "order" in your table, as you have pasted it above, does not really exist. SQL tables are based on unordered tuples of records. Regarding your observation, the output you are seeing is consistent with the ordering you specified in the ORDER BY clause. If you want the original ordering, then you should maintain a second column which preserves this ordering.

By the way, with the advent of MySQL 8+, you may now take advantage of the analytic functions. In this case, ROW_NUMBER comes in handy:

SELECT val, ROW_NUMBER() OVER (ORDER BY val) row_num
FROM data
ORDER BY val;

The user variables in your question are heading towards being deprecated, so the above version is the way go here.

user_18632290
Related