Introduction

This only serves as a temporary solution until a better solution is available.
The following solution has a limitation which is that the keyword ASC and DESC of all query variables within ORDER BY must be the same. (For example, 3 variables must be ASC, ASC, ASC not ASC, ASC, DESC.

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.

Figure 1: Example list used for this article.

Steps Example 

Step 1

Go to list then go to Data and select Database SQL Query as the source of data as shown in Figure 2.

Figure 2: Select Database SQL Query

Step 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 in figure 3.

Figure 3: Inserted script

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

Step 3

Set the Order and Order by properties in the list settings

Figure 4: List properties configuration

Step 4

Result

Figure 5: Result

Sample App

APP_kb-dx8_listUsingMultipleColumnsByDefaultExample.jwa


  • No labels