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.

score 40 · Accepted Answer · edited Mar 04 '21 at 02:25

40

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.

edited Mar 04 '21 at 02:25

PolyGeo

65,136
29
109
338

answered May 03 '16 at 18:37

ArMoraer

5,649
2
26
48

she_weeds · Answer 2 · 2023-04-05T12:35:55.977

14

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:

array_agg all the $id values into an array, ordered by ORDER_FIELD
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)

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

edited Apr 05 '23 at 12:35

answered May 03 '20 at 07:35

she_weeds

12,488
1
28
58

I don't understand what is happening in your GIF. Before you apply your code, your table appears to be unsorted. After the code, the table is sorted by the field NUM. Was that your intention? If so, where in your code is the field NUM specified? When I apply your code with a shapefile, the output sorting is by FID. I don't mean to sound unappreciative, because I think you may be on to something important: what if I want to sequentially number a new field based on the current table order, even if the table is unsorted? That is, I want to "freeze" the order as currently displayed. – Stu Smith May 03 '20 at 16:28
Sorry, I should have been more consistent or explicit with the example. The table was originally sorted by an id column (irrelevant, really), then I created a new column order with sequential numbers (1-13) based on the num field (=ORDER_FIELD in the code). Then I sorted the table by order to show that num has been ordered as well. Your attribute table would almost always be sorted, if not by some field or combination of fields, then the underlying sequence of data in the file (which is the "default/original sorting", if you like - I think this order is accessed using $id). – she_weeds May 03 '20 at 21:54
Unlike Excel, GIS formats rely on database formats. You can't really "lock" the sequence the data except as how it was inserted. You use data attributes to order the records as needed (and the original sequence is itself stored as an auto incrementing integer, hidden or otherwise). In Excel the sequence you see is part of the data and can be changed around arbitrarily by moving/inserting rows in between existing ones. If your attribute table order in qgis cannot be explained by the data, and it isn't the original sequence in the shapefile itself, then there's no way to renumber except manually – she_weeds May 03 '20 at 22:06

score 11 · Answer 3 · edited Aug 04 '21 at 05:31

11

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

edited Aug 04 '21 at 05:31

Taras

32,823
4
66
137

answered May 03 '16 at 05:19

Pierma

901
4
10

Thank you, I still work with long term release 2.8 so it was my first time I discovered virtual layer in 2.14. Apart it was very very very slow it works. Generally this is acceptable though I am going to wait a bit more for some other answers if any. – Miro May 03 '16 at 07:24

Assigning row number for sorted table by column in QGIS

3 Answers3

Linked

Related