16

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.

2

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.

  • 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 at 17:36
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.

  • 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 '16 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 '16 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 '16 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 '16 at 12:29
  • Nice Tool... saved me a lot of work...much appreciated – K_Man May 28 '18 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.

  • 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 '16 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 '16 at 13:04
5

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!!!

  • 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 '16 at 5:10
  • 2
    You can work with .SHP for this solution - this does not change the data format. Try it out... – DPSSpatial May 26 '16 at 12:51
  • 1
    @GeorgeC did you try this out? – DPSSpatial Jun 21 '17 at 15:01
5

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.

  • 1
    Successfully tested with QGIS 3.6. – Mr. Che Aug 9 at 4:12
  • 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 at 11:19
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

  • 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 '18 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 '18 at 7:09
1

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.

  • 2
    +1 for the correct note that it's currently not possible but -1 for suggesting messing with the DBF in Excel – underdark Dec 3 '12 at 20:54
  • I did say it was 'nasty' :) – MappaGnosis Dec 3 '12 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 '12 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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