ORDER BY¶

Sorts query results by one or more expressions in ascending or descending order.

Syntax¶

The ORDER BY clause is used in SELECT statements:

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC, column2 DESC

Parameters¶

ORDER BY expression [ASC|DESC] [NULLS FIRST|NULLS LAST], ...

Sorts rows based on the values of one or more expressions. Results are ordered by the first expression, then by the second expression for rows with equal first expression values, and so on.

expression

Can be:

Column names
Nested field references (e.g., struct_column.field)
Columns not in the SELECT list

ASC (optional, default)

Sort in ascending order (smallest to largest)

DESC (optional)

Sort in descending order (largest to smallest)

NULLS FIRST (optional)

Place NULL values at the beginning of the result set

NULLS LAST (optional)

Place NULL values at the end of the result set

By default, NULL values sort as follows: - With ASC: NULLs come last (equivalent to ASC NULLS LAST) - With DESC: NULLs come first (equivalent to DESC NULLS FIRST)

Returns¶

Returns all selected rows sorted according to the specified order. The sorting ensures that rows are ordered based on the values in the ORDER BY columns. However, the relative order of rows that compare as equal according to all specified ORDER BY columns is not guaranteed to be stable or deterministic and may vary between executions.

Examples¶

Setup¶

For these examples, assume we have a products table:

CREATE TABLE products(
    id BIGINT,
    name STRING,
    category STRING,
    price BIGINT,
    PRIMARY KEY(id))

CREATE INDEX price_idx AS SELECT price, category FROM products ORDER BY price
CREATE INDEX category_idx AS SELECT category, price FROM products ORDER BY category
CREATE INDEX category_price_idx AS SELECT category, price FROM products ORDER BY category, price

INSERT INTO products VALUES
    (1, 'Widget A', 'Electronics', 100),
    (2, 'Widget B', 'Electronics', 150),
    (3, 'Gadget X', 'Electronics', 200),
    (4, 'Tool A', 'Hardware', 80),
    (5, 'Tool B', 'Hardware', 120)

ORDER BY Single Column¶

Sort products by price in ascending order:

SELECT name, price
FROM products
ORDER BY price

`name`	`price`
`"Tool A"`	`80`
`"Widget A"`	`100`
`"Tool B"`	`120`
`"Widget B"`	`150`
`"Gadget X"`	`200`

ORDER BY DESC¶

Sort products by price in descending order:

SELECT name, price
FROM products
ORDER BY price DESC

`name`	`price`
`"Gadget X"`	`200`
`"Widget B"`	`150`
`"Tool B"`	`120`
`"Widget A"`	`100`
`"Tool A"`	`80`

ORDER BY Multiple Columns¶

Sort by category, then by price within each category:

SELECT category, name, price
FROM products
ORDER BY category, price

`category`	`name`	`price`
`"Electronics"`	`"Widget A"`	`100`
`"Electronics"`	`"Widget B"`	`150`
`"Electronics"`	`"Gadget X"`	`200`
`"Hardware"`	`"Tool A"`	`80`
`"Hardware"`	`"Tool B"`	`120`

ORDER BY with Mixed Directions¶

Sort by category ascending, price descending:

SELECT category, name, price
FROM products
ORDER BY category ASC, price DESC

This query requires an index with matching sort order: ORDER BY category, price DESC

ORDER BY with WHERE¶

Combine filtering with sorting:

SELECT name, price
FROM products
WHERE price >= 100
ORDER BY price

`name`	`price`
`"Widget A"`	`100`
`"Tool B"`	`120`
`"Widget B"`	`150`
`"Gadget X"`	`200`

ORDER BY Non-Projected Column¶

You can order by columns not in the SELECT list:

SELECT name
FROM products
ORDER BY price

`name`
`"Tool A"`
`"Widget A"`
`"Tool B"`
`"Widget B"`
`"Gadget X"`

The query planner will use an index that includes the ordering column, even if it’s not projected in the result.

ORDER BY on Nested Fields¶

For tables with struct types, you can order by nested fields:

CREATE TYPE AS STRUCT address_type(city STRING, zipcode INTEGER)
CREATE TABLE customers(
    id BIGINT,
    name STRING,
    address address_type,
    PRIMARY KEY(id))

CREATE INDEX city_idx AS SELECT address.city FROM customers ORDER BY address.city

SELECT name, address.city
FROM customers
ORDER BY address.city

NULL Handling¶

By default, NULL values have specific sort positions:

With ASC: NULL values appear last
With DESC: NULL values appear first

You can override this behavior with NULLS FIRST or NULLS LAST:

-- NULLs at the beginning (overriding ASC default)
SELECT name, rating
FROM products
ORDER BY rating ASC NULLS FIRST

-- NULLs at the end (overriding DESC default)
SELECT name, rating
FROM products
ORDER BY rating DESC NULLS LAST

Note: Using NULLS FIRST or NULLS LAST requires an index with matching NULL ordering, similar to the constraint for mixed ASC/DESC ordering.

Important Notes¶

Index Requirement¶

ORDER BY operations require an index with matching sort order. FRL does not perform in-memory sorting. The query planner must find an index that satisfies the ordering requirement.

Example index for ORDER BY price:

CREATE INDEX price_idx AS SELECT price FROM products ORDER BY price

Without a suitable index, the query will fail with an “unable to plan” error (0AF00).

See Indexes for details on creating indexes that support ORDER BY operations.

Mixed Ordering Constraints¶

Mixed ordering (e.g., ORDER BY a ASC, b DESC) is only supported if a matching index exists with that exact ordering:

-- This requires an index: ORDER BY category ASC, price DESC
SELECT * FROM products ORDER BY category ASC, price DESC

Without a matching index, mixed ordering queries will fail with error 0AF00.

To create a matching index:

CREATE INDEX cat_price_desc_idx AS
    SELECT category, price FROM products
    ORDER BY category ASC, price DESC

Subquery Restrictions¶

ORDER BY is not allowed in subqueries or nested SELECT statements:

-- ERROR: ORDER BY in subquery not allowed
SELECT * FROM (SELECT * FROM products ORDER BY price) AS sub

-- ERROR: ORDER BY in EXISTS subquery not allowed
SELECT * FROM products WHERE EXISTS
    (SELECT * FROM products ORDER BY price LIMIT 1)

This restriction is due to the architecture’s requirement for index-backed operations.

Pagination¶

For large result sets, use JDBC’s maxRows parameter for pagination with continuations:

Statement stmt = conn.createStatement();
stmt.setMaxRows(10);  // Fetch 10 rows at a time
ResultSet rs = stmt.executeQuery("SELECT * FROM products ORDER BY price");

Continuations allow stateless pagination without LIMIT/OFFSET syntax.

Note: SQL LIMIT ... OFFSET syntax is not supported. Use JDBC’s maxRows parameter instead.

Execution Model¶

FRL does not perform in-memory sorting. All ORDER BY operations must be backed by an index with compatible ordering. This is a fundamental architectural constraint that ensures queries can execute efficiently over large datasets.

The query planner will: 1. Look for an index with matching sort order 2. Use that index to scan results in the correct order 3. Fail with error 0AF00 if no suitable index exists