Select, Filter, and Sort

A useful query answers a precise question: which rows, which columns, and in what presentation order? SQL is declarative—you describe the result, and the database chooses an execution plan.

Prerequisites

Complete Relational Data Models and know the grain of customers and orders.

Learning goals

• Use SELECT, aliases, FROM, WHERE, and ORDER BY.
• Separate filtering from sorting.
• Make limited results deterministic with a complete tie-breaker.
• Recognize portable syntax versus DuckDB conveniences.

Logical query stages

Although SELECT is written first, a simple query is easiest to reason about as FROM → WHERE → SELECT → ORDER BY → LIMIT. WHERE removes rows; SELECT calculates output columns; ORDER BY arranges the final rows. Without ORDER BY, row order is unspecified—even if a small table appears stable today.

Prefer named columns over SELECT * in durable analysis. An explicit projection documents the output contract and does not silently expand when a source table gains a column.

Worked example

This Standard SQL query joins customer names to orders, filters shipped rows, then sorts by amount and a unique order identifier.

SELECT o.order_id, c.customer_name, o.amount
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.status = 'shipped'
ORDER BY o.amount DESC, o.order_id;

order_id	customer_name	amount
105	Cy	80.00
102	Ada	60.00
101	Ada	40.00

LIMIT is widely implemented, but the SQL standard spells row limiting as FETCH FIRST … ROWS ONLY. DuckDB supports both. Here the ordering is unique, so the first three rows are reproducible.

SELECT o.order_id, c.customer_name
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
ORDER BY o.ordered_at, o.order_id
FETCH FIRST 3 ROWS ONLY;

order_id	customer_name
101	Ada
102	Ada
103	Ben

DuckDB-specific: SELECT * EXCLUDE (...), ORDER BY ALL, and FROM-first syntax are useful conveniences, but avoid presenting them as portable Standard SQL.

Limitations and common mistakes

• LIMIT 10 without an ORDER BY asks for any ten qualifying rows.
• Ordering only by a non-unique value leaves ties nondeterministic.
• Text ordering depends on collation; binary UTF-8 order is not every language's dictionary order.
• WHERE sees input columns, not most aliases created later by SELECT.

Practice

Return the two largest non-cancelled orders. Include order_id as a tie-breaker, and list only the three columns the consumer needs.

Next steps

The next lesson explains why ordinary comparisons do not match missing values: NULLs and three-valued logic.

Primary sources

Checked 2026-10-14: DuckDB SELECT, WHERE, and ORDER BY; PostgreSQL SELECT is used as a second maintained implementation reference for portable behavior.