PostgreSQL ORDER BY

Key Concepts

ORDER BY price ASC

ORDER BY price DESC

ORDER BY country, city

ORDER BY 1, 2

🔹 Basic ORDER BY

The ORDER BY clause sorts results in ascending order by default. You can explicitly specify ASC for ascending or DESC for descending order. Sorting helps organize data logically, making it easier to find patterns, identify extremes, and present information clearly.

-- Sort by price (ascending - default)
SELECT product_name, price 
FROM products 
ORDER BY price;

-- Sort by price (descending)
SELECT product_name, price 
FROM products 
ORDER BY price DESC;

-- Sort by name alphabetically
SELECT name, email 
FROM customers 
ORDER BY name ASC;

🔹 Sorting by Multiple Columns

You can sort by multiple columns to create hierarchical ordering. PostgreSQL sorts by the first column, then by the second column for rows with the same first column value, and so on. This creates organized, multi-level sorting perfect for complex data organization.

-- Sort by country, then by city
SELECT name, city, country 
FROM customers 
ORDER BY country, city;

-- Sort by category ascending, then price descending
SELECT product_name, category, price 
FROM products 
ORDER BY category ASC, price DESC;

-- Sort by multiple columns with different directions
SELECT name, age, salary 
FROM employees 
ORDER BY age DESC, salary ASC;

🔹 Sorting with Column Positions

Instead of column names, you can use column position numbers in ORDER BY. The first column in SELECT is 1, the second is 2, and so on. This shorthand is convenient for quick queries but using column names is recommended for clarity and maintainability.

-- Sort by first column (product_name), then second (price)
SELECT product_name, price, category 
FROM products 
ORDER BY 1, 2;

-- Sort by third column descending
SELECT name, email, created_date 
FROM users 
ORDER BY 3 DESC;

-- Mix column names and positions
SELECT product_name, price, stock 
FROM products 
ORDER BY category, 2 DESC;

🔹 Sorting with NULL Values

By default, PostgreSQL places NULL values last in ascending order and first in descending order. You can control NULL placement using NULLS FIRST or NULLS LAST keywords. This gives you precise control over how missing data appears in sorted results.

-- Default: NULLs last in ascending order
SELECT name, phone 
FROM customers 
ORDER BY phone ASC;

-- Put NULLs first
SELECT name, phone 
FROM customers 
ORDER BY phone ASC NULLS FIRST;

-- Put NULLs last in descending order
SELECT name, salary 
FROM employees 
ORDER BY salary DESC NULLS LAST;

🔹 Sorting with Expressions

You can sort by calculated expressions, functions, or transformations rather than just column values. This allows sorting by string length, date parts, mathematical calculations, or any computed value. It's powerful for custom sorting logic based on derived data.

-- Sort by string length
SELECT name 
FROM customers 
ORDER BY LENGTH(name);

-- Sort by year from date
SELECT order_id, order_date 
FROM orders 
ORDER BY EXTRACT(YEAR FROM order_date);

-- Sort by calculated value
SELECT product_name, price, stock, (price * stock) AS total_value 
FROM products 
ORDER BY total_value DESC;

-- Sort by lowercase name (case-insensitive)
SELECT name 
FROM customers 
ORDER BY LOWER(name);