ORDER BY clause

Back to DuckDB Data Engineering Glossary

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;