Advanced sorting in Visual SQL

When creating Queries in Visual Mode, Chartio 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 then by the categorical columns, you could set that in the Query Sort.

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

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.

Note: Updating the SQL query in SQL Mode will disconnect Visual Mode and SQL Mode for the chart. In other words, making subsequent changes to your dataset in Visual Mode after editing the SQL Mode query will no longer update the auto-generated query in SQL Mode.

Sort Rows in the Pipeline

Add a Sort Rows Action to the Pipeline, which allows you to re-sort the columns in your result set in any order and direction. This Action 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 Actions.

1. Use Transpose to swap the rows for columns and columns for rows.
2. Once transposed, use Reorder Columns to drag your columns (i.e., your rows) in the desired order.
3. Finally, add one last Transpose to revert your columns back to rows.

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, select Custom as the formula type, then enter your CASE statement. For this example, we want to apply a custom sort order to our “Score” column, where “good” should be first, “bad” should be second, and “offered” should be third.

    case
        when "Score" = 'good' then 1
        when "Score" = 'bad' then 2
        when "Score" = 'offered' then 3
    end
   

2. After creating the new column, you’d then use the Sort Rows Action to apply the desired sort order to your columns.

   

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