Assigning row number for sorted table by column in QGIS

Question

In QGIS 3 the Field calculator has a great function called @row_number (in QGIS 2 it was $rownum) which returns (assigns) the number of the current row.

Is there a way to make it work together with sorting?

Let's say I want to sort table by column "length" and get the order (sequence) of lines by length from shortest to longest.

I know I can use some office spreadsheet software easily for this to sort by column and populate new column with order (sequence). But I would prefer to do the whole job in QGIS.

As workaround there is also Changing order of features in shapefile - using MMQGIS Plugin and function modify/sort and after that use @row_number ($rownum) on the new saved file.

Answer 1

I have 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.

Answer 2

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

From QGIS 3.8 the aggregating functions have 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. array_agg all the $id values into an array, ordered by ORDER_FIELD

2. Look up the position, in this newly ordered array, of the current feature's $id value (starts from 0 - add a +1 at the end so it starts from 1)

3. Output the new numbering to another column for sorting by.

   Expression (QGIS 3.0-3.6):

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

   Expression (QGIS 3.8+)

   array_find(
       array_agg($id,
           order_by:="ORDER_FIELD")
       ,$id)
   +1
   

Example:

---

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 array_agg()

• 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

Answer 3

I'm not sure you can with field calculator, but you can use a "Virtual Layer" through Layer > Add Layer > Add/Edit 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