Semantics

Sorting

Define how query results should be sorted using semantic order sequences.

Overview

Sorting is a core component of semantic metrics that defines how query results should be ordered. It provides sophisticated result sorting through semantic order sequences that work seamlessly with measures, dimensions, and raw columns. The ordering system allows you to define how query results should be sorted using business-friendly references rather than raw SQL column names.

Diagram showing how sorting integrates with the metric architecture

Use Cases

Sorting is used for:

Revenue Analysis: Sort by revenue measures in descending order
Time Series: Sort by time dimensions chronologically
Performance Metrics: Sort by KPIs to identify top performers
Data Exploration: Sort by various dimensions for analysis
Dashboard Display: Control how data appears in visualizations

Syntax

Type: SemanticOrderSequence

List of order sequences defining how to sort the query results. Each sequence specifies what to sort by and how to sort it.

{
    "order": [
        {
            "name": "revenue_sort",
            "description": "Sort by total revenue descending",
            "semantic_type": "measure",
            "semantic_name": "total_revenue",
            "order_type": "DESC",
            "nulls": "LAST"
        },
        {
            "name": "month_sort",
            "description": "Sort by month ascending",
            "semantic_type": "dimension",
            "semantic_name": "month",
            "order_type": "ASC",
            "nulls": "FIRST"
        }
    ]
}

Parameters

name

Type: str
Required: Yes

Unique identifier name for this order sequence. Used to reference the ordering in queries and API calls.

{
    "name": "revenue_sort"
}

description

Type: Optional[str]
Default: None
Required: No

Human-readable explanation of what this ordering represents. This helps other developers understand the sorting logic.

{
    "description": "Sort by total revenue in descending order"
}

semantic_type

Type: Optional[SemanticOrderReferenceType]
Default: None
Required: No

Type of semantic reference for ordering. This determines how the ordering reference will be resolved.

Available Values:

measure: Reference to a measure by name (recommended)
dimension: Reference to a dimension by name (recommended)
column: Direct column reference (legacy)
position: Position-based ordering (1-indexed)

{
    "semantic_type": "measure"
}

semantic_name

Type: Optional[str]
Default: None
Required: No

Name of the measure or dimension to order by. Used when semantic_type is measure or dimension.

{
    "semantic_name": "total_revenue"
}

position

Type: Optional[int]
Default: None
Required: No

1-based position in SELECT clause for ordering. Used when semantic_type is position.

{
    "position": 1
}

query

Type: Optional[str]
Default: None
Required: No

Column name or SQL expression that defines the sorting criteria. Used for direct column ordering (legacy compatibility).

{
    "query": "created_at"
}

table

Type: Optional[str]
Default: None
Required: No

Source table or view where this order column resides. Used with query for direct column ordering.

{
    "table": "orders"
}

order_type

Type: SemanticOrderType
Default: ASC
Required: No

The sort direction for this ordering sequence.

Available Values:

ASC: Ascending order (lowest to highest)
DESC: Descending order (highest to lowest)

{
    "order_type": "DESC"
}

nulls

Type: Optional[SemanticNullsPosition]
Default: None
Required: No

Specification of where null values should appear in the sort order.

Available Values:

FIRST: Place null values at the beginning
LAST: Place null values at the end

{
    "nulls": "LAST"
}

Patterns

Revenue Sorting

{
    "order": [
        {
            "name": "revenue_desc",
            "description": "Sort by revenue descending",
            "semantic_type": "measure",
            "semantic_name": "total_revenue",
            "order_type": "DESC",
            "nulls": "LAST"
        }
    ]
}

Time-Based Sorting

{
    "order": [
        {
            "name": "date_asc",
            "description": "Sort by date ascending",
            "semantic_type": "dimension",
            "semantic_name": "month",
            "order_type": "ASC",
            "nulls": "FIRST"
        }
    ]
}

Multi-Level Sorting

{
    "order": [
        {
            "name": "revenue_desc",
            "description": "Primary sort by revenue",
            "semantic_type": "measure",
            "semantic_name": "revenue",
            "order_type": "DESC",
            "nulls": "LAST"
        },
        {
            "name": "category_asc",
            "description": "Secondary sort by category",
            "semantic_type": "dimension",
            "semantic_name": "product_category",
            "order_type": "ASC",
            "nulls": "FIRST"
        },
        {
            "name": "region_asc",
            "description": "Tertiary sort by region",
            "semantic_type": "dimension",
            "semantic_name": "region",
            "order_type": "ASC",
            "nulls": "FIRST"
        }
    ]
}

Position-Based Sorting

{
    "order": [
        {
            "name": "first_column",
            "description": "Sort by first column",
            "semantic_type": "position",
            "position": 1,
            "order_type": "ASC",
            "nulls": "FIRST"
        }
    ]
}

Legacy Column Sorting

{
    "order": [
        {
            "name": "created_at_desc",
            "description": "Sort by creation date",
            "semantic_type": "column",
            "query": "created_at",
            "table": "orders",
            "order_type": "DESC",
            "nulls": "LAST"
        }
    ]
}

Default Ordering Rules

When no explicit ordering is specified, Cortex applies intelligent default sorting:

Rule Priority

First time dimension with granularity (ascending)
First measure (descending)
First dimension (ascending)

Example Default Behavior

{
    "name": "sales_analysis",
    "measures": [
        {
            "name": "revenue",
            "type": "sum",
            "query": "amount"
        }
    ],
    "dimensions": [
        {
            "name": "month",
            "query": "DATE_TRUNC('month', sale_date)"
        },
        {
            "name": "product_category",
            "query": "category"
        }
    ]
}

Generated ORDER BY:

ORDER BY "month" ASC, "revenue" DESC, "product_category" ASC

Advanced Features

Conditional Ordering

Apply different sorting based on conditions:

{
    "order": [
        {
            "name": "high_value_sort",
            "description": "Sort high-value items by revenue",
            "semantic_type": "measure",
            "semantic_name": "revenue",
            "order_type": "DESC",
            "condition": "revenue > 1000"
        },
        {
            "name": "low_value_sort",
            "description": "Sort low-value items by category",
            "semantic_type": "dimension",
            "semantic_name": "category",
            "order_type": "ASC",
            "condition": "revenue <= 1000"
        }
    ]
}

Dynamic Ordering

Use parameters to control sorting:

{
    "parameters": {
        "sort_by": {
            "name": "sort_by",
            "type": "string",
            "allowed_values": ["revenue", "count", "date"],
            "default": "revenue"
        },
        "sort_direction": {
            "name": "sort_direction", 
            "type": "string",
            "allowed_values": ["ASC", "DESC"],
            "default": "DESC"
        }
    },
    "order": [
        {
            "name": "dynamic_sort",
            "description": "Dynamic sorting based on parameters",
            "semantic_type": "measure",
            "semantic_name": "{{sort_by}}",
            "order_type": "{{sort_direction}}",
            "nulls": "LAST"
        }
    ]
}

Performance Considerations

Indexing

Ensure proper database indexes for frequently sorted columns:

-- Create index for common sorting patterns
CREATE INDEX idx_sales_date_revenue ON sales_transactions (sale_date, amount DESC);

-- Composite index for multi-level sorting
CREATE INDEX idx_sales_category_revenue ON sales_transactions (category, amount DESC);

Query Optimization

Use appropriate data types for sorting
Consider query result size when sorting
Use LIMIT clauses with sorting for large datasets
Monitor query performance with complex sorting

Caching

Sorting results can be cached for better performance:

{
    "name": "cached_sorted_metrics",
    "cache": {
        "enabled": true,
        "ttl": 3600
    },
    "order": [
        {
            "name": "revenue_sort",
            "semantic_type": "measure",
            "semantic_name": "revenue",
            "order_type": "DESC"
        }
    ]
}

Best Practices

Use Semantic References: Prefer semantic_type and semantic_name over direct column references
Add Descriptions: Always include descriptions to help other developers understand the sorting logic
Consider Performance: Index frequently sorted columns and monitor query performance
Handle Nulls Appropriately: Use nulls parameter to control null value positioning
Use Consistent Patterns: Apply consistent sorting patterns across related metrics

Video Tutorial

Video: Step-by-step guide to configuring sorting in the Cortex dashboard

Measures Configuration - Learn about configuring measures for sorting
Dimensions Configuration - Understand dimension-based sorting
Query Engine - Learn about order processing
Metrics Configuration - Complete metric configuration guide

Edit this pageorReport an issue

Filters

Data filtering conditions for metrics, allowing you to focus on specific subsets of data and create targeted analytics.

Parameters

Enable dynamic query generation by allowing metrics to accept runtime values, making metrics flexible and reusable.