> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cube.dev/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).

## Data modeling

Consider the following data model:

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

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:

```sql title="SQL" theme={"dark"}
SELECT value
FROM sort_nulls
GROUP BY 1
ORDER BY 1 ASC;
```

```json title="JSON" theme={"dark"}
{
  "dimensions": [
    "sort_nulls.value"
  ],
  "order": {
    "sort_nulls.value": "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:

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

```json title="JSON" theme={"dark"}
{
  "dimensions": [
    "sort_nulls.value",
    "sort_nulls.value_for_sorting"
  ],
  "order": {
    "sort_nulls.value_for_sorting": "asc"
  }
}
```

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]: https://docs.cube.dev/reference/core-data-apis/queries.md

[ref-data-apis]: https://docs.cube.dev/reference#data-apis


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.