Skip to main content

ORDER BY clause

The ORDER BY clause sorts the result set of a query by one or more columns or expressions. It can be used in standard SELECT statements and within window function OVER clauses.

Syntax

SELECT ...
FROM ...
ORDER BY sort_expression [ASC | DESC], ...

Within a window function:

function_name() OVER (
  [PARTITION BY partition_expression, ...]
  ORDER BY sort_expression [ASC | DESC] [NULLS FIRST | NULLS LAST], ...
  [window_frame_clause]
)

  • sort_expression: A column, expression, or column alias to sort by.

  • ASC (default): Sorts in ascending order.

  • DESC: Sorts in descending order.

  • NULLS FIRST: Places NULL values before non-NULL values.

  • NULLS LAST: Places NULL values after non-NULL values.

NULL Sorting Behavior

Window function ORDER BY

In window function ORDER BY clauses, CeloSQL enforces consistent NULL placement across all target engines:

  • ASC (default): NULLs sort last (NULLS LAST)

  • DESC: NULLs sort first (NULLS FIRST)

User-specified NULLS FIRST or NULLS LAST in window functions is always respected.

Standard ORDER BY

NULL placement in standard ORDER BY depends on the target engine’s default behavior and may differ between engines.

Engine-specific defaults

Spark-based engine

In these environments, NULL is consistently treated as the smallest possible value:

  • ASC: NULL values appear first.

  • DESC: NULL values appear last.

  • Syntax support: NULLS FIRST and NULLS LAST are supported in both standard and window function ORDER BY clauses.

Vertica

In these environments, the default placement of NULL values in a standard ORDER BY varies by the column’s data type:

  • Sorted first: INTEGER, BIGINT, DECIMAL, DATE, TIME, TIMESTAMP, and INTERVAL.

  • Sorted last: DOUBLE, FLOAT, VARCHAR, and BOOLEAN.

  • Syntax support: NULLS FIRST and NULLS LAST are supported only within window functions. Standard ORDER BY clauses follow the type-based defaults listed above.

Returns

  • Returns the result set sorted by the specified columns and directions.

  • When multiple sort expressions are provided, rows are sorted by the first expression, then by the second expression for rows with equal values in the first, and so on.

Limits / Notes

  • ORDER BY can reference column aliases defined in the SELECT list.

  • ORDER BY can reference columns by their position (1-based index) in the SELECT list.

  • Sorting behavior for special floating-point values: NaN and Infinity are valid DOUBLE values and follow their own sorting rules, which may differ between engines.

Examples

Basic ORDER BY

-- Sort employees by salary in descending order
> SELECT name, salary
  FROM employees
  ORDER BY salary DESC;

Multiple sort expressions

-- Sort by department ascending, then by salary descending within each department
> SELECT name, department, salary
  FROM employees
  ORDER BY department ASC, salary DESC;

Window function ORDER BY with NULL handling

-- NULLs are placed last for ASC ordering in window functions
> SELECT name, salary,
         RANK() OVER (ORDER BY salary ASC) as salary_rank
  FROM employees;

-- NULLs are placed first for DESC ordering in window functions
> SELECT name, salary,
         ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num
  FROM employees;

-- User-specified NULLS FIRST is respected in window functions
> SELECT name, salary,
         RANK() OVER (ORDER BY salary ASC NULLS FIRST) as salary_rank
  FROM employees;