Is there a way to assign row number for sorted table by column in QGIS?

So, I've coded a plugin (Sort and Number) to solve your issue. It allows you to select up to 3 fields and order your attribute table according to these fields. Then, it numbers the attribute table in a new field (named "order" by default), starting from 1.

enter image description here


I'm not sure you can with field calculator, but you can use a virtual layer with a query like :

SELECT 
    * ,
    (SELECT count(*) FROM your_layer AS b WHERE a.length >= b.length) AS length_position
FROM your_layer AS a 
ORDER BY a.length

For a simple sort (by one column) you can use QGIS expressions in the Field Calculator.

From QGIS 3.8 the aggregate() function has allowed for the use of an order field (which can be different from the aggregated field).

This means you can use something like the following expression to:

  1. concatenate all the $id values into a comma-separated list, ordered by ORDER_FIELD
  2. Convert the ordered list into an array
  3. Look up the position, in the array, of the current feature's $id value (converted to string) (starts from 0 - add a +1 at the end so it starts from 1)

Expression:

array_find(
    string_to_array(
        concatenate(expression:=to_string($id),
                    concatenator:=',',
                    order_by:="ORDER_FIELD"),
        ','),
    to_string($id))
+1

Example: enter image description here


Other notes:

  • $id is always unique and sequential and recalculated even after features are added/removed so you don't need to rely on a separate unique ID column.

  • If ORDER_FIELD has numbers in a text column or an alphanumeric combination (e.g. '1', '2', '3', '3A', '4') you can use on-the-fly conversion (using to_int("ORDER_FIELD")) or zero-padding (e.g. lpad("ORDER_FIELD",3,'0')) to ensure it sorts in the correct order.

  • To reverse the order use array_reverse() between array_find() and string_to_array()

  • To order by multiple fields you could concatenate them in the order_by parameter (e.g. "textfield"||"ORDER_FIELD") - just be sure to zero-pad any numeric fields so that it sorts by A01,A02....A11,B1, not A1,A11,A12...A2,B1