Skip to main content

​
Implementing custom sorting

In this recipe, you will learn how to sort the result set of a query by your custom criteria.

​
Use case

While data APIs provide built-in ways to sort the result set by dimensions and measures in ascending or descending order, sometimes you may need more flexibility. For example, if a measure or a dimension contains NULL values, they will always appear last last when sorting in the ascending order. This recipe shows how to work around this behavior for all data APIs.
Currently, the SQL API does not support ORDER BY ... NULLS FIRST/LAST. Please track this issue.

​
Data modeling

Consider the following data model: 
cubes:
  - name: sort_nulls
    sql: |
      SELECT 1234 AS value UNION ALL
      SELECT 5678 AS value UNION ALL
      SELECT NULL AS value

    dimensions:
      - name: value
        sql: value
        type: number

      - name: value_for_sorting
        sql: "COALESCE({value}, 0)"
        type: number
You can see that the value dimension contains NULL values while the value_for_sorting dimension never has NULL values. It means that sorting by the latter dimension will always strictly follow the ascending or descending order. Moreover, note that this additional dimension that acts as a sorting key may reference more than one other dimension, allowing to move your complex sorting logic from the querying layer to your data model.

​
Query

Let’s query the value dimension and sort the result set by that dimension in the ascending order: 
SELECT value
FROM sort_nulls
GROUP BY 1
ORDER BY 1 ASC;
We’ll get the following result set:
value
1234
5678
NULL
Now, let’s query the value dimension but sort the result set by the value_for_sorting dimension in the ascending order: 
SELECT value, value_for_sorting
FROM sort_nulls
GROUP BY 1, 2
ORDER BY 2 ASC;
We’ll get the following result set:
valuevalue_for_sorting
NULL0
12341234
56785678
As you can see, now NULL values of the value dimension appear first in the result set.