Can I replace in MySQL all values in the column by their positions in the ordered list of values

I have the following table:

| col1 | col2 | col3 |
| 1    | 1.1  | 9.3  |
| 2    | 7.9  | 1.3  |
| 3    | 3.7  | 7.3  |
| 4    | 9.0  | 5.7开发者_如何转开发  |

I need to order the rows using the weighted sum of the col2 and col3. It can be easily done in the following way:

select * from mytable order by (0.8*col2 + 0.2*col3)

Now I want to do almost the same, the only difference is that I need to replace the actual values in the col2 and col3 by there position in the ordered list of all values in the column. For example, in the second column the following replacements should be done:

(1.1 -> 1, 3.7 -> 2, 7.9 -> 3, 9.0 -> 4)

Does anybody know if there is an easy way to construct a query that would do that. In other words, I would like to have something like that:

select * from mytable order by (0.8*modified(col2) + 0.2*modified(col3))

where the modified function replace the value by its position in the ordered list of all the values from the column.

You can try something like this:

SET @cnt := 0;
update mytable m ,
(
SELECT
    @cnt := @cnt + 1 as newid,
    col1
 from mytable order by (0.8*col2 + 0.2*col3)
) as mysorted
set a.col2 = mysorted.newid
where a.col1 = mysorted.col1