ORDER BY

Learning Focus

Use this lesson to master ORDER BY for sorting results in DuckDB queries.

Concept Overview

ORDER BY sorts the result set by one or more columns. By default, ordering is ascending (ASC).

DuckDB also supports NULLS FIRST / NULLS LAST and ordering by expressions.

Basic Syntax

SELECT columns
FROM table
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

Examples

Example 1: Sort by Salary (Descending)

SELECT name, salary FROM employees ORDER BY salary DESC;

Example 2: Multi-Column Sort

SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

Example 3: Sort by Expression

SELECT name, salary,
       salary / 12 AS monthly
FROM employees
ORDER BY monthly DESC;

Example 4: NULL Handling

-- NULLs last (default in DuckDB for ASC)
SELECT name, hire_date FROM employees ORDER BY hire_date ASC NULLS LAST;

-- NULLs first
SELECT name, hire_date FROM employees ORDER BY hire_date ASC NULLS FIRST;

Example 5: Positional Ordering

-- Order by the 3rd column in SELECT list
SELECT name, department, salary FROM employees ORDER BY 3 DESC;

Best Practices

• Always pair ORDER BY with LIMIT for pagination
• Avoid ORDER BY in subqueries when not needed (adds overhead)
• Use column names rather than positions for clarity

Common Pitfalls

Pitfall	Consequence	Prevention
Omitting ASC/DESC	Default ASC applied silently	Be explicit for documentation clarity
Ordering a subquery unnecessarily	Wasted computation	Only ORDER BY in the outermost query
NULL sorting surprises	NULLs appear at unexpected positions	Always add NULLS FIRST or NULLS LAST

Quick Reference

ORDER BY col ASC
ORDER BY col DESC
ORDER BY col1 ASC, col2 DESC
ORDER BY col NULLS FIRST
ORDER BY col NULLS LAST
ORDER BY 2 DESC  -- positional

What's Next

• Next: INSERT INTO - Add rows to DuckDB tables.
• Module Overview - Return to this module index.