ORDER BY clause

Overview

The ORDER BY clause is a fundamental SQL command that lets you control the sequence in which your query results are returned. It appears near the end of a SQL query and sorts the rows based on one or more columns you specify.

Basic Usage

In DuckDB, you can sort results in either ascending (ASC) or descending (DESC) order. If you don't specify a direction, ascending order is used by default. Here's a simple example:

Copy code
SELECT name, age 
FROM users
ORDER BY age DESC;

Multiple Column Sorting

You can sort by multiple columns, creating a hierarchy of sort criteria. Each subsequent column is used to break ties in the previous columns:

Copy code
SELECT city, state, population
FROM cities
ORDER BY state ASC, population DESC;

DuckDB Extensions

DuckDB adds some helpful extensions to standard SQL sorting. You can use ORDER BY ALL to sort by all columns in the SELECT list from left to right:

Copy code
SELECT city, state, population
FROM cities
ORDER BY ALL;

You can also reference column aliases defined in the SELECT clause directly in your ORDER BY, which isn't always supported in other databases:

Copy code
SELECT 
  first_name || ' ' || last_name AS full_name,
  age
FROM users
ORDER BY full_name;

Performance Considerations

DuckDB implements sorting using a vectorized quicksort algorithm, making it highly efficient for large datasets. However, sorting large result sets can still be memory-intensive, so it's good practice to combine ORDER BY with LIMIT when you only need a subset of sorted results:

Copy code
SELECT city, population
FROM cities
ORDER BY population DESC
LIMIT 10;