> ## Documentation Index
> Fetch the complete documentation index at: https://cubed3-feat-druid-driver-streaming.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# 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][ref-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.

<Note>
  Currently, the SQL API does not support `ORDER BY ... NULLS FIRST/LAST`.
  Please [track this issue](https://github.com/cube-js/cube/issues/8464).
</Note>

## Data modeling

Consider the following data model:

<CodeGroup>
  ```yaml title="YAML" theme={null}
  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
  ```

  ```javascript title="JavaScript" theme={null}
  cube(`sort_nulls`, {
    sql: `
      SELECT 1234 AS value UNION ALL
      SELECT 5678 AS value UNION ALL
      SELECT NULL AS value
    `,

    dimensions: {
      value: {
        sql: `value`,
        type: `number`
      },

      value_for_sorting: {
        sql: `COALESCE(${value}, 0)`,
        type: `number`
      }
    }
  })
  ```
</CodeGroup>

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:

<CodeGroup>
  ```sql title="SQL" theme={null}
  SELECT value
  FROM sort_nulls
  GROUP BY 1
  ORDER BY 1 ASC;
  ```

  ```json title="JSON" theme={null}
  {
    "dimensions": [
      "sort_nulls.value"
    ],
    "order": {
      "sort_nulls.value": "asc"
    }
  }
  ```
</CodeGroup>

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:

<CodeGroup>
  ```sql title="SQL" theme={null}
  SELECT value, value_for_sorting
  FROM sort_nulls
  GROUP BY 1, 2
  ORDER BY 2 ASC;
  ```

  ```json title="JSON" theme={null}
  {
    "dimensions": [
      "sort_nulls.value",
      "sort_nulls.value_for_sorting"
    ],
    "order": {
      "sort_nulls.value_for_sorting": "asc"
    }
  }
  ```
</CodeGroup>

We'll get the following result set:

| value  | value\_for\_sorting |
| ------ | ------------------- |
| `NULL` | 0                   |
| 1234   | 1234                |
| 5678   | 5678                |

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

[ref-queries]: /reference/queries

[ref-data-apis]: /reference#data-apis