24

Is it possible to sort multiple fields at the same time in QGIS?

For example, I'd like to sort a table by state name, and then within each state, sort by county name.

This would be the same as the Advanced Sort option available in ArcMap.

Improve this question
0

10 Answers 10

Reset to default
15

This can now be done without any plugins or database-specific work, at least for version 3.2+. To sort by multiple columns:

  1. Right click on the attribute table column header and choose "Sort..."
  2. Add the function concat() along with the fields you want to sort by, in the proper order (ex. concat("column1", "column2")). An output preview should be shown below your code.
  3. Check or uncheck the "Sort Ascending" checkbox at the bottom to configure the column sort directions.
  4. Click OK, and confirm the sort.

This functionality was added via issue #1137, and covered in the latest (development) version of the user manual.

Improve this answer
2
  • 1
    Successfully tested with QGIS 3.6.
    – Comrade Che
    Aug 9, 2019 at 4:12
  • 1
    I've concatenated string and an integer that does not have a fixed length, so I used a work-around to zero-fill the integer to a fixed length string: concat("col1", right( ('000' || tostring( "col2" )), 4))
    – NettaB
    Sep 5, 2019 at 11:19
8

You can use the Sort and Number plugin. It allows you to sort an attribute table by up to 3 fields, then it creates an additional field that contains the order of the sorted features.

Disclaimer: I'm the author of the plugin.

Improve this answer
5
  • hopefully it will work. I set it onto my 150k record dataset and it's still going after 10mins. This hardcodes the sort in the new column so you can then sort by that -is there a way that works like in excel or ArcMap that doesn't add a column? see desktop.arcgis.com/en/arcmap/10.3/manage-data/tables/…
    – GeorgeC
    May 25, 2016 at 12:03
  • 1
    I also hope it will work, I never tested it on such large datasets. I don't think it is possible to simply change the display order, like in ArcMap; QGIS API doesn't provide any method to do so. That's why I chose to add a numbering field as a workaround.
    – ArMoraer
    May 25, 2016 at 12:08
  • It's still going -ArcMap did it in about 30s. Oh well. Thanks for the solution though -I'll keep this open for 24h to see if anyone has any other suggestions.
    – GeorgeC
    May 25, 2016 at 12:21
  • Ok, thanks for the feedback. The plugin relies directly on Python's sort() function, I wonder why it is so slow. I'll try to find some trick to make the process faster in a future version.
    – ArMoraer
    May 25, 2016 at 12:29
  • Nice Tool... saved me a lot of work...much appreciated
    – K_Man
    May 28, 2018 at 9:29
7

If is OK to save the result permanently you can sort the shapefile with GDAL

ogr2ogr -f "ESRI Shapefile" -dialect sqlite -sql "SELECT *
FROM my_shapefile ORDER BY attr_1, attr_2, attr_3" sorted.shp my_shapefile.shp

Took less that 3 minutes with a shapefile having one million polygons.

Improve this answer
2
  • 1
    thanks but I prefer not to do anything permanent as it will generate multiple datasets and also I was hoping for a way from within QGIS itself.
    – GeorgeC
    May 25, 2016 at 12:47
  • 2
    Then I would probably convert the shapefile into Spatialite. With DB Manager you can then build and run SQL queries. You can also save the frequently used queries for future needs.
    – user30184
    May 25, 2016 at 13:04
7

The DB Manager plugin in QGIS now has a 'Virtual Layers' node that allows you to write SQL commands against shapefiles loaded into your QGIS project.

So now that you have this ability, the SQL logic to sort by multiple fields is in the ORDER BY statement, using asc (ascending), desc (Descending), and you can string them together to sort by multiple columns:

enter image description here

An added bonus is you'll learn more SQL so when you get sick of shapefiles and most to PostGIS the leap won't be so great!!!

Improve this answer
4
  • Thanks -this is a great solution however for this use case I don't want to change the data format to anything else either.
    – GeorgeC
    May 26, 2016 at 5:10
  • 2
    You can work with .SHP for this solution - this does not change the data format. Try it out...
    – DPSSpatial_BoycottingGISSE
    May 26, 2016 at 12:51
  • 1
    @GeorgeC did you try this out?
    – DPSSpatial_BoycottingGISSE
    Jun 21, 2017 at 15:01
  • So far the only solution to also sort a print atlas by two values asc and desc :)
    – MrXsquared
    Nov 25, 2021 at 17:00
6

There is actually a very simple trick. Suppose you want to sort by field "A" first, and then by field "B" within "A". First left click on the header of the field "B" (click once for ascending sort, a second time for descending) in the attribute table. Then left click on the header of field "A" (once for ASC, 2nd time for DESC). This last action will have sorted field "A", while preserving the sorting of field "B" within identical values of "A". Tried it with 3 fields and it also works (then got bored). I'm using QGIS 3.6.3-Noosa.

Improve this answer
3
  • 1
    Excellent! I also tested it with 3 fields (state, county, city) and it worked perfectly - ascending or descending. 3.8.3.
    – Stu Smith
    Oct 9, 2019 at 17:36
  • Well phooey. At 3.12.3 this no longer works. I am sad.
    – Stu Smith
    Jul 4, 2020 at 15:13
  • Wait! 20 minutes later and this technique works! Why now and not earlier? I am confused...
    – Stu Smith
    Jul 4, 2020 at 15:38
2

Well, it is 2018 now so I can't believe this is not answered yet.

  1. Open the attribute table view and right-click on any column, then choose Sort...

  2. Dialog window Configure attribute table sort order is open now with full expression editor as well

  3. Use COALESCE function with the desired columns, for example, this will sort a table by 3 columns (first by ID then VERSION and then fid)

coalesce("ID","VERSION","fid" )

enter image description here

Clicking OK will sort the table

Improve this answer
2
  • Unfortunately, coalesce did not work for me. In a test using a shapefile attribute table with two integer columns (both containing a mix of numbers and blank values): 1) with the Sort ascending button unchecked, the result sorted the first column correct (descending) but the second column was incorrectly sorted ascending for each value within the first column. 2) with the Sort ascending button checked, the result was a random result that made no sense.
    – Stu Smith
    Jul 16, 2018 at 5:41
  • 1
    You can concatenate fields by "||" operator, coalesce will return the first non NULL value which is not valid for sorting. Probably you should use some formatting to have 1 before 11.
    – Zoltan
    Jul 23, 2018 at 7:09
1

PyQgis solution:

# set order by field (twice)
clause1 = qgis.core.QgsFeatureRequest.OrderByClause(field1, ascending=True)
clause2 = qgis.core.QgsFeatureRequest.OrderByClause(field2, ascending=True)
orderby = qgis.core.QgsFeatureRequest.OrderBy([clause1, clause2])
request.setOrderBy(orderby)

layer = qgis.core.QgsVectorLayer('layer_path', 'layer_name', "provider") # add layer
features = layer.getFeatures(request)

for feature in features:
    attrs = feature.attributes()
    print(attrs)
Improve this answer
0

You can't (at least not at the moment). Sorry!

See the QGIS documentation and scroll down to the very last line.

A nasty work-around would be to pull the Shapefile's DBF into Excel. Sort it there and save the result. Alternatively import your Shapefile into PostGIS or SpatialLite where you have greater sorting functionality. Basically, you will have to by-pass QGIS for advanced sorting.

Improve this answer
3
  • 4
    +1 for the correct note that it's currently not possible but -1 for suggesting messing with the DBF in Excel
    – underdark ♦
    Dec 3, 2012 at 20:54
  • I did say it was 'nasty' :)
    – MappaGnosis
    Dec 3, 2012 at 21:04
  • You mention PostGIS and SpatiaLite: could you show an example of how to do what the OP asked for from within QGIS, also using a db-plugin?
    – steko
    Dec 13, 2012 at 16:15
0

As suggested by @Sylvester Sneekly, if your data is loaded into an RDBMS like PosGIS or SpatialLite, you could consider creating a database view in the RDBMS that presorts the tabular data, and then load the presorted view into QGIS as a layer.

Improve this answer
0

I know this is old, but I keep ending up here from a search. Adding on to other answers, and using QGIS 3.28 Firenze.

Right-click "sort..." on a column to get the expression editor.

Sort multiple string columns (from Eron Lloyd's answer):

concat("col1", "col2", "col3")

Sort mixed string and numeric columns, use lpad for the numeric columns and multiply to remove the decimal if needed:

concat("str_col", lpad("int_col", 10, 0), lpad(round("decimal_col",2),10,0))

You may need to adjust the lpad second argument larger if you’ve got a bigger number range.

For real numbers, the decimal throws the sorting off. Rounding to a consistent number of decimals resolves this. I haven’t seen this documented, which is why I’m posting.

Using lpad is the workaround for mixed data types, suggested here in the 3.28 manual.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.