How to Sort List Using Multiple Columns

Introduction

This article showcases the implementation of sorting the order of multiple columns in a list. This approach is useful when you want to create a ranking or ordering of your data with multiple levels of priority. Each column in the ORDER BY clause contributes to the sorting order, and the result is a well-defined sequence of rows in the result set.

How does it work?

To sort a list using multiple columns, you follow these steps:

1. Go to the list settings, select the data source, and choose Database SQL Query, as shown below.
   
2. Once inside the configuration, make changes to the following script and insert it into the SQL SELECT Query field. Make sure to insert the correct primary key as shown below.
   
   
   

   1. Primary Sorting (<column_name1> ASC):

      • The result set is first sorted by the <column_name1> column in ascending order (ASC stands for ascending).
      • This means that rows with earlier <column_name1> will come first in the result set.

   2. Secondary Sorting (<column_name2>  ASC):

      • In case there are rows with the same <column_name1>, those rows are further sorted by the <column_name2> column in ascending order.
      • This ensures that within each group of rows with the same <column_name1>, the rows will be ordered by increasing <column_name2>.

   3. Tertiary Sorting (<column_name3> ASC):

      • If there are still rows with the same <column_name1> and <column_name2>, the rows are finally sorted by the <column_name3> column in ascending order.
      • This provides a finer level of sorting, ensuring that within each subgroup of rows with the same <column_name1> and <column_name2>, the rows will be ordered by <column_name3>.
   4. The result set is structured in a way that it's first sorted by <column_name1> then by <column_name2>, and finally by <column_name3>. The ROW_NUMBER() function assigns a unique sequential index (sorted_index) to each row based on this specific order.
   Script:
   

   --Script--
   -- this method sorts the table with correct order_by and creates an index column for it.
   -- this method requires user to select the order by <index name> in the list properties page as seen Figure 4
   -- <column_name> & <table_name> are placeholder. Replace placeholders with desired value.
    
   SELECT ROW_NUMBER() OVER (ORDER BY <column_name1> ASC, <column_name2> ASC, <column_name3> ASC) AS sorted_index, <column_name>, <column_name> , <column_name> , <column_name>, <column_name>
   FROM <table_name>

3. Configure the Order and Order By properties in the list settings to use the sorted_index created by the SQL query.
   

Expected outcome

Check the results in your list to ensure the sorting is applied correctly.

Download sample app