Use advanced sorting methods in Visual SQL

Summary

When creating queries in visual mode, Atlassian Analytics creates the ORDER BY clause of the generated SQL query by listing the categorical columns in the order that they're chosen in the query and sorts each column in ascending order by default. Aggregated columns are not included in the autogenerated query, but you can add them and change the column sort order and directions by editing “Query sort” in the advanced drawer. For instance, if you wanted to sort by the aggregated column first and then by the categorical columns, you could set that in the “Query sort”.

You also have a couple of other options for performing advanced sorting on your result set.

Solution

Ascending/descending sorting

SQL mode

Switch your chart to SQL mode and edit the SQL to change the order of the sorted columns in the ORDER BY clause.

Sort rows in the pipeline

Add a “Sort rows” step in Visual SQL, which allows you to re-sort the columns in your result set in any order and direction. This step performs a nested sort, just like the type of sort you can apply in the query.

Custom sorting

Transpose and reorder

Another quick option would be to leverage the “Transpose” and “Reorder columns” steps.

1. Use a “Transpose” step to swap the rows for columns and columns for rows.

2. Once transposed, use a “Reorder columns” step to drag your columns (i.e., your rows) in the desired order.

3. Finally, add one last “Transpose” step to revert your columns back to rows.

The example above leverages the transpose-reorder-transpose method to make the statuses of ‘Open', ‘Closed’, and 'Resolved’ the first three rows of data in the chart.

Create a sort column using a CASE statement

If you have a specific order for values in a column (that can’t be done with a simple ascending or descending sort direction), you can create a new temporary column that uses a CASE statement to assign a sort priority to each row.

1. Add a “Formula column” step, select Custom as the formula type, then enter your CASE statement. For this example, we want to apply a custom sort order to our "Status" column, where ‘Open’ should be first, ‘Closed’ should be second, ‘Resolved’ should be third, and any other status would be ordered last.

   1 2 3 4 5 6 CASE WHEN "Status" = 'Open' THEN 1 WHEN "Status" = 'Closed' THEN 2 WHEN "Status" = 'Resolved' THEN 3 ELSE 4 END
   
   

2. After creating the new column, you’d then use the “Sort rows” step to apply the desired sort order to your columns.

   

3. Finally, you can hide the temporary column from your result set.