# Sorting & Limiting

## Overview

Sorting and limiting control the order and size of a semantic query's result set. Add sort orders to arrange result rows by a field, an alias, or an aggregated measure, and add a limit to cap the number of rows returned. Both are set per query in the `options` block of `structuredSemanticQuery`.

Sorting is expressed as a list of sort orders (`sort_orders`), applied in the order you list them: the first sort order is the primary key, each subsequent one breaks ties. Two sort-order shapes are available — a `simple_sort_order` that orders by a single field or alias, and an `aggregative_sort_order` that orders a grouped dimension by an aggregated measure. You can mix both in one request; a common pattern is an aggregative sort followed by a simple tie-breaker.

Limiting caps the number of result rows and is applied after grouping. Because the limit applies to grouped output, it constrains the number of groups returned, not the number of underlying source records.

## Metadata in the model

Sorting has an optional model-defined counterpart. A field's default sort direction is set at authoring time through the `SortOrder` attribute (values `Ascending`, `Descending`, `None`) on a Semantic Data Object Field, a Semantic Calculated Dimension, or a Semantic Calculated Measurement. Set `apply_model_sorting: true` in the query to apply those model-defined sort directions to the result. Limiting has no model metadata — it is set per query.

For how sort direction is defined in the model, see [Semantic Data Object Field](/docs/data/semantic-layer/guide/connect-api-in-depth-entities.md#semantic-data-object-field-semanticdataobjectfield), [Semantic Calculated Dimension](/docs/data/semantic-layer/guide/connect-api-in-depth-entities.md#semantic-calculated-dimension-semanticcalcdimension), and [Semantic Calculated Measurement](/docs/data/semantic-layer/guide/connect-api-in-depth-entities.md#semantic-calculated-measurement-semanticcalcmeasurement) in the Authoring API.

## Query usage

### Simple sort order

Use `simple_sort_order` to order results by a single field or alias. Reference the sort key either by `sort_by_field` (a full field expression) or by `sort_by_field_alias` (an alias already assigned in the query's `fields`). List multiple entries in `sort_orders` to sort by more than one key; they apply in order.

```json
{
  "structured_semantic_query": {
    "fields": [
      {
        "expression": {
          "table_field": {
            "name": "Account Name",
            "table_name": "AccountSemanticLayer__dll"
          }
        },
        "alias": "abc",
        "row_grouping": true
      },
      {
        "expression": {
          "table_field": {
            "name": "Annual Revenue",
            "table_name": "AccountSemanticLayer__dll"
          }
        },
        "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM",
        "alias": "abc_sum"
      }
      // ...
    ],
    "options": {
      "sort_orders": [
        {
          "simple_sort_order": {
            "sort_by_field_alias": "abc"
          }
        },
        {
          "simple_sort_order": {
            "sort_by_field_alias": "abc_sum"
          }
        },
        {
          "simple_sort_order": {
            "sort_by_field": {
              "expression": {
                "table_field": {
                  "name": "Annual Revenue",
                  "table_name": "AccountSemanticLayer__dll"
                }
              },
              "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_AVG"
            },
            "null_values_position": "NULLS_FIRST"
          }
        }
      ]
    }
  }
  // ...
}
```

### Aggregative sort order

Use `aggregative_sort_order` to order a grouped dimension by an aggregated measure — for example, sort each account group by its `SUM(Annual Revenue)`. Identify the grouped column with `grouped_field_alias` (an alias) or `grouped_expression` (a field expression), and specify the measure to sort by with `sort_by_field`, `sort_by_field_alias`, or `sort_by_row_count: true` to sort by the group's row count. Aggregative and simple sort orders combine in one `sort_orders` list.

```json
{
  "structured_semantic_query": {
    "fields": [
      // ...
    ],
    "options": {
      "sort_orders": [
        {
          "aggregative_sort_order": {
            "grouped_field_alias": "abc",
            "sort_by_field_alias": "abc_sum"
          }
        },
        {
          "aggregative_sort_order": {
            "grouped_expression": {
              "table_field": {
                "name": "Mailing City",
                "table_name": "ContactSemanticLayer__dll"
              }
            },
            "sort_by_field": {
              "expression": {
                "table_field": {
                  "name": "Annual Revenue",
                  "table_name": "AccountSemanticLayer__dll"
                }
              },
              "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM"
            },
            "null_values_position": "NULLS_FIRST"
          }
        },
        {
          "aggregative_sort_order": {
            "grouped_expression": {
              "table_field": {
                "name": "Mailing Street",
                "table_name": "ContactSemanticLayer__dll"
              }
            },
            "sort_by_row_count": true,
            "null_values_position": "NULLS_FIRST"
          }
        }
      ],
      "detailed_rows": true
    }
  }
  // ...
}
```

### Sort direction

Set `sorting_order` on a sort order to `ASC` (ascending) or `DESC` (descending). When omitted, results sort ascending. This applies to both `simple_sort_order` and `aggregative_sort_order`. You can sort by an alias assigned to a calculated field in the query.

```json
{
  "structured_semantic_query": {
    "fields": [
      {
        "expression": {
          "calculated_field": {
            "name": "simple_calc",
            "expression": "123",
            "calculated_measure_expression": {
              "measure_output_type": "SEMANTIC_MEASUREMENT_TYPE_NUMBER"
            }
          }
        },
        "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM",
        "alias": "Simple Calc"
      }
      // ...
    ],
    "options": {
      "sort_orders": [
        {
          "simple_sort_order": {
            "sort_by_field_alias": "Simple Calc",
            "sorting_order": "DESC"
          }
        }
      ]
    }
  }
  // ...
}
```

### Null value positioning

Set `null_values_position` on a sort order to control where rows with null sort-key values land: `NULLS_FIRST` places them before non-null rows, `NULLS_LAST` places them after. This applies to both sort-order shapes. The example above under **Simple sort order** sets `"null_values_position": "NULLS_FIRST"`, and the example below sets `NULLS_LAST`.

```json
{
  "structuredMetricQuery": {
    "sort_orders": [
      {
        "simple_sort_order": {
          "sort_by_field": {
            "expression": {
              "table_field": {
                "name": "Name",
                "table_name": "SemanticAccount_SDO"
              }
            }
          },
          "sorting_order": "ASC",
          "null_values_position": "NULLS_LAST"
        }
      }
    ]
    // ...
  },
  "semanticModelId": "2SMxx0000004CAeGAM"
}
```

### Sorting rules by data type

When you sort by a field without overriding the direction, results order by the field's data type: dimension columns sort alphanumerically, table fields sort by their underlying type (numeric fields sort numerically), and date and datetime fields sort chronologically. The following example sorts a date field descending.

```json
{
  "structuredSemanticQuery": {
    "options": {
      "sort_orders": [
        {
          "simple_sort_order": {
            "sort_by_field_alias": "CreatedDate",
            "sorting_order": "DESC"
          }
        }
      ]
    },
    "semantic_context": {
    },
    "fields": [
    ]
  },
  "semanticModelId": "2SMxx0000004CAeGAM"
}
```

### Model sorting

Set `apply_model_sorting: true` to apply the model-defined `SortOrder` of each queried field to the result. In the following example the queried fields carry a model `sortOrder` (`Descending` on the dimension, `Ascending` on the calculated measurement), and the query opts in to those directions.

```json
{
  "structured_semantic_query": {
    "fields": [
      {
        "expression": {
          "table_field": {
            "name": "semantic__Name__c",
            "table_name": "SemanticAccount__dlm"
          }
        }
      },
      {
        "expression": {
          "semantic_field": {
            "name": "formula_measurement_example"
          }
        }
      }
      // ...
    ],
    "options": {
      "apply_model_sorting": true
    }
  },
  "semantic_model": {
    "apiName": "Sales",
    "semanticDataObjects": [
      {
        "apiName": "SemanticAccount__dlm",
        "dataObjectName": "ssot__Account__dlm",
        "dataObjectType": "Dmo",
        "semanticDimensions": [
          {
            "apiName": "semantic__Name__c",
            "dataType": "Text",
            "dataObjectFieldName": "ssot__Name__c",
            "sortOrder": "Descending"
          }
          // ...
        ]
      }
    ],
    "semanticCalculatedMeasurements": [
      {
        "apiName": "formula_measurement_example",
        "dataType": "Number",
        "expression": "10 * inner_formula_measurement_example",
        "sortOrder": "Ascending"
      }
      // ...
    ]
  }
}
```

### Limit

Use `limit_options.limit` to cap the number of result rows. The limit is applied after grouping, so it constrains the number of groups returned rather than the number of underlying source records. For a result grouped by dimension, a limit of 30 returns at most 30 groups even when the source has more.

```json
{
  "structuredSemanticQuery": {
    "fields": [
      // ...
    ],
    "options": {
      "sort_orders": [
        // ...
      ],
      "limit_options": {
        "limit": 2000
      }
    }
  }
  // ...
}
```

> **Performance:** A `limit` caps the number of result rows after grouping — it constrains the groups returned, not the rows scanned. Use filters to reduce the scan.

> **Performance:** Result size is capped at 5,000 rows for interactive queries and 50,000 rows for non-interactive queries; a query that would exceed the cap fails. Narrow the result with filters or a `limit`.

### Detailed rows

Set `detailed_rows: true` to return the individual detail rows behind an aggregated result alongside the aggregates, rather than only the grouped/aggregated output.

```json
{
  "structured_semantic_query": {
    "fields": [
      {
        "expression": {
          "table_field": {
            "name": "Account Name",
            "table_name": "AccountSemanticLayer__dll"
          }
        },
        "row_grouping": true
      },
      {
        "expression": {
          "table_field": {
            "name": "Annual Revenue",
            "table_name": "AccountSemanticLayer__dll"
          }
        },
        "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM"
      }
      // ...
    ],
    "options": {
      "detailed_rows": true,
      "subtotals": false,
      "grand_total": false,
      "row_counts": false
    }
  }
  // ...
}
```

> **Performance:** When you request detailed (non-aggregated) rows, a `limit` is pushed into the detail-rows step, reducing the rows scanned and returned.

### Row counts

Set `row_counts: true` to include row-count information in the query result. Combine it with `sort_by_row_count` (see **Aggregative sort order**) to sort groups by how many rows each contains.

```json
{
  "structured_semantic_query": {
    "fields": [
      // ...
    ],
    "options": {
      "sort_orders": [
        // ...
      ],
      "subtotals": true,
      "grand_total": true,
      "row_counts": true
    }
  }
  // ...
}
```

## Reference

| Field (wire name)        | Type                 | Required | Description                                                                                                    |
| ------------------------ | -------------------- | -------- | -------------------------------------------------------------------------------------------------------------- |
| `sort_orders`            | SortOrder[]          | N        | Ordered list of sort orders; each is a `simple_sort_order` or `aggregative_sort_order`. Applied in list order. |
| `simple_sort_order`      | SimpleSortOrder      | N        | Sort by a single field or alias.                                                                               |
| `aggregative_sort_order` | AggregativeSortOrder | N        | Sort a grouped dimension by an aggregated measure or row count.                                                |
| `sort_by_field`          | SortField            | N*       | Field expression to sort by (optionally with `semantic_aggregation_method`).                                   |
| `sort_by_field_alias`    | String               | N*       | Alias (assigned in `fields`) to sort by.                                                                       |
| `sort_by_row_count`      | bool                 | N*       | In an aggregative sort, sort by the group's row count.                                                         |
| `grouped_field_alias`    | String               | N*       | Alias of the grouped dimension in an aggregative sort.                                                         |
| `grouped_expression`     | Expression           | N*       | Field expression of the grouped dimension in an aggregative sort.                                              |
| `sorting_order`          | SortingOrder         | N        | Sort direction: `ASC` or `DESC`. Defaults to ascending.                                                        |
| `null_values_position`   | NullValuesPosition   | N        | `NULLS_FIRST` or `NULLS_LAST`.                                                                                 |
| `apply_model_sorting`    | bool                 | N        | Apply each field's model-defined `SortOrder` to the result.                                                    |
| `limit_options.limit`    | int                  | N        | Maximum number of result rows; applied after grouping.                                                         |
| `detailed_rows`          | bool                 | N        | Return individual detail rows in addition to aggregated results.                                               |
| `row_counts`             | bool                 | N        | Include row-count information in the result.                                                                   |

For the full request schema, see [Request Reference](/docs/data/semantic-layer/guide/query-api-in-depth-fields.md).

## Limitations

- **A sort order references exactly one key.** Provide one of `sort_by_field` / `sort_by_field_alias` (and, for aggregative sorts, one of `grouped_field_alias` / `grouped_expression`); the alias must match a field defined in the query.
- **The limit value must be non-negative.**
- **`limit` is applied after grouping.** It caps the number of grouped result rows, not the number of underlying source records, so it directly affects how a grouped visualization renders.

## Related

- [Grouping](/docs/data/semantic-layer/guide/query-api-in-depth-grouping.md) — Grouping drives the aggregation that aggregative sorts and limits operate on.
- [Aggregation & Totals](/docs/data/semantic-layer/guide/query-api-in-depth-aggregation.md) — Aggregation methods used in `sort_by_field` and row counts/totals.
- [Semantic Data Object Field](/docs/data/semantic-layer/guide/connect-api-in-depth-entities.md#semantic-data-object-field-semanticdataobjectfield) — Authoring API for a field's model-defined sort order.
- [Semantic Calculated Dimension](/docs/data/semantic-layer/guide/connect-api-in-depth-entities.md#semantic-calculated-dimension-semanticcalcdimension) · [Semantic Calculated Measurement](/docs/data/semantic-layer/guide/connect-api-in-depth-entities.md#semantic-calculated-measurement-semanticcalcmeasurement) — Model-defined sort order on calculated fields.
- [Request Reference](/docs/data/semantic-layer/guide/query-api-in-depth-fields.md) — Complete field schema for query options and sort orders.