How to add a new column in attribute table with attribute with indexes of another max-min ordered attribute field?

Question

I've an attribute column named e.g. VALUE with different values I would like to make a column named e.g. ORDERsuch that the greatest value has the attribute 1 the second value 2 etc. etc. so order express the order from the biggest to the smallest value like this:

VALUE   ORDER
12000     1
8000      2
600       3
500       4
200       5

So far I've tried with QGIS 1.8 to order the VALUE column (that please note is not in the right order by default) from maxto minsimply clicking over the attribute name and than use $rownumin field calculator but this seems use the default rows numeration so I don't have the right order.

Please edit my question if you think you can explain it better!

Answer 1

Here's a funny trick using a Spatialite query to get the values in descending order. If you do a count() and use a WHERE clause to get values >= the current value, then you'll actually be getting the order, so if I have a table "mypoints" with a column "value" and an empty column of "value_order" then:

SELECT value, (
SELECT count(*) FROM mypoints AS my 
WHERE my.value>=mypoints.value) AS orders
FROM mypoints 
ORDER BY value DESC;

gives a list of the values and their order. I can then update the "value_order" column like so:

UPDATE mypoints SET val_order = (
SELECT count(*) FROM mypoints AS my 
WHERE my.value>=mypoints.value
);

HTH

Answer 2

I guess you could first create a new, sorted layer (How to permanently rearrange the order of features in a Shapfile with QGIS?) and then use Field calculator $rownum.