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.

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:

We'll get the following result set:

Now, let's query the value dimension but sort the result set by the value_for_sorting dimension in the ascending order:

We'll get the following result set:

As you can see, now NULL values of the value dimension appear first in the result set.