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
``````

Top