Versions Compared

Old Version 3

changes.mady.by.user Maxson Chen

Saved on

compared with

New Version Current

changes.mady.by.user Aadrian Zarin

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Table of Contents

...

Introduction

Warning

This only serves as a temporary solution until Joget make one or a better solution is available.
The  The following solution has a limitation that 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 showcase showcases the implementation of sort sorting the order of multiple column 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.

Image Modified

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

Info

  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
  1. The ROW_NUMBER() function assigns a unique sequential index (sorted_index) to each row based on this specific order.


Code Block
languagesql
--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

View file
nameAPP_kb-dx8_listUsingMultipleColumnsByDefaultExample.jwa
height250

...