PostgreSQL ORDER BY

Overview

The ORDER BY clause is used to sort the result set by one or more columns in ascending or descending order. It is one of the most commonly used clauses in SQL queries.

Basic Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

Ascending Order (ASC)

By default, ORDER BY sorts in ascending order (ASC).

-- Sort by price (ascending)
SELECT * FROM products ORDER BY price;

-- Explicit ASC keyword
SELECT * FROM products ORDER BY price ASC;

-- Sort names alphabetically
SELECT name, email FROM users ORDER BY name;

-- Sort dates (oldest first)
SELECT * FROM orders ORDER BY order_date;

Descending Order (DESC)

Use DESC to sort in descending order.

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

-- Sort dates (newest first)
SELECT * FROM orders ORDER BY order_date DESC;

-- Sort by quantity (highest first)
SELECT product_name, stock FROM inventory ORDER BY stock DESC;

Sorting by Multiple Columns

You can sort by multiple columns, with each column having its own sort direction.

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

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

-- Mixed sort directions
SELECT * FROM orders 
ORDER BY status ASC, order_date DESC, total DESC;

Sorting by Column Position

You can reference columns by their position number in the SELECT list.

-- Sort by first column
SELECT name, price FROM products ORDER BY 1;

-- Sort by second column descending
SELECT name, price FROM products ORDER BY 2 DESC;

-- Multiple columns by position
SELECT name, category, price FROM products 
ORDER BY 2, 3 DESC;

Sorting with Expressions

You can sort by calculated expressions or functions.

-- Sort by calculated value
SELECT name, price, price * 1.1 AS price_with_tax
FROM products
ORDER BY price * 1.1 DESC;

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

-- Sort by absolute value
SELECT value FROM numbers ORDER BY ABS(value);

-- Sort by date part
SELECT * FROM orders 
ORDER BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date);

Sorting with CASE

Use CASE expressions for custom sort orders.

-- Custom priority order
SELECT * FROM tasks
ORDER BY 
    CASE priority
        WHEN 'critical' THEN 1
        WHEN 'high' THEN 2
        WHEN 'medium' THEN 3
        WHEN 'low' THEN 4
    END;

-- Sort with conditional logic
SELECT * FROM products
ORDER BY 
    CASE 
        WHEN category = 'Featured' THEN 1
        WHEN category = 'New' THEN 2
        ELSE 3
    END,
    price DESC;

Sorting NULL Values

Control where NULL values appear in sorted results.

-- NULL values last (default for ASC)
SELECT * FROM users ORDER BY phone;

-- NULL values first
SELECT * FROM users ORDER BY phone NULLS FIRST;

-- NULL values last (explicit)
SELECT * FROM users ORDER BY phone NULLS LAST;

-- DESC with NULL handling
SELECT * FROM products ORDER BY discount DESC NULLS LAST;

Sorting with Aggregates

Sort results of aggregate queries.

-- Sort by count
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
ORDER BY product_count DESC;

-- Sort by sum
SELECT user_id, SUM(total) AS total_spent
FROM orders
GROUP BY user_id
ORDER BY total_spent DESC;

-- Sort by average
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
ORDER BY avg_price DESC;

Sorting with JOINs

Sort results from joined tables.

-- Sort by column from joined table
SELECT u.name, o.order_number, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
ORDER BY o.total DESC;

-- Sort by multiple tables
SELECT u.name, o.order_date, p.product_name
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
ORDER BY u.name, o.order_date DESC;

Sorting with Subqueries

-- Sort by subquery result
SELECT 
    u.name,
    (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count
FROM users u
ORDER BY order_count DESC;

-- Sort by correlated subquery
SELECT 
    p.product_name,
    p.price,
    (SELECT AVG(price) FROM products WHERE category = p.category) AS category_avg
FROM products p
ORDER BY p.price - (SELECT AVG(price) FROM products WHERE category = p.category) DESC;

Sorting Text

Special considerations for text sorting.

-- Case-insensitive sort
SELECT name FROM users ORDER BY LOWER(name);

-- Sort by specific collation
SELECT name FROM users ORDER BY name COLLATE "en_US";

-- Natural sort (numeric strings)
SELECT version FROM software ORDER BY version::text;

-- Sort by substring
SELECT email FROM users ORDER BY SUBSTRING(email FROM '@(.*)$');

Sorting Dates and Times

-- Sort by date
SELECT * FROM orders ORDER BY order_date;

-- Sort by time
SELECT * FROM events ORDER BY event_time;

-- Sort by timestamp
SELECT * FROM logs ORDER BY created_at DESC;

-- Sort by date part
SELECT * FROM orders 
ORDER BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date);

-- Sort by age
SELECT name, birth_date FROM users 
ORDER BY AGE(birth_date) DESC;

Sorting with DISTINCT

-- Sort distinct values
SELECT DISTINCT category FROM products ORDER BY category;

-- Sort distinct with multiple columns
SELECT DISTINCT country, city FROM users 
ORDER BY country, city;

Sorting with LIMIT

Combine ORDER BY with LIMIT for top-N queries.

-- Top 10 most expensive products
SELECT * FROM products 
ORDER BY price DESC 
LIMIT 10;

-- Top 5 customers by spending
SELECT user_id, SUM(total) AS total_spent
FROM orders
GROUP BY user_id
ORDER BY total_spent DESC
LIMIT 5;

-- Pagination
SELECT * FROM products 
ORDER BY id 
LIMIT 20 OFFSET 40;  -- Page 3 (rows 41-60)

Performance Considerations

-- Create index for frequently sorted columns
CREATE INDEX idx_products_price ON products(price);
CREATE INDEX idx_orders_date ON orders(order_date DESC);

-- Composite index for multiple sort columns
CREATE INDEX idx_products_category_price ON products(category, price DESC);

-- Use EXPLAIN to analyze sort performance
EXPLAIN ANALYZE
SELECT * FROM products ORDER BY price DESC;

-- Check if sort is using index
EXPLAIN SELECT * FROM products ORDER BY price;
-- Look for "Index Scan" instead of "Sort"

Common Patterns

Top N per Group

-- Top 3 products per category by price
WITH ranked AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rank
    FROM products
)
SELECT * FROM ranked WHERE rank <= 3;

Random Order

-- Random order
SELECT * FROM products ORDER BY RANDOM();

-- Random sample
SELECT * FROM products ORDER BY RANDOM() LIMIT 10;

Alphabetical with Numbers

-- Natural sort for alphanumeric strings
SELECT name FROM items 
ORDER BY 
    REGEXP_REPLACE(name, '[^0-9]', '', 'g')::int,
    name;

Best Practices

1. Use indexes for frequently sorted columns

   CREATE INDEX idx_orders_date ON orders(order_date DESC);

2. Avoid sorting large result sets

   -- Good: Filter first, then sort
   SELECT * FROM orders 
   WHERE order_date >= '2024-01-01'
   ORDER BY order_date DESC;
   
   -- Avoid: Sort everything, then filter
   SELECT * FROM orders 
   ORDER BY order_date DESC
   LIMIT 1000;

3. Use column aliases for clarity

   SELECT 
       name,
       price * quantity AS total_value
   FROM order_items
   ORDER BY total_value DESC;

4. Specify sort direction explicitly

   -- Good: Explicit
   SELECT * FROM products ORDER BY price DESC;
   
   -- Avoid: Implicit (less clear)
   SELECT * FROM products ORDER BY price;

5. Consider NULL handling

   SELECT * FROM users 
   ORDER BY last_login DESC NULLS LAST;

Common Mistakes

-- ❌ Sorting by alias in WHERE (not allowed)
SELECT price * 1.1 AS total FROM products 
WHERE total > 100  -- Error!
ORDER BY total;

-- ✅ Use expression in WHERE
SELECT price * 1.1 AS total FROM products 
WHERE price * 1.1 > 100
ORDER BY total;

-- ❌ Sorting by column not in SELECT with DISTINCT
SELECT DISTINCT category FROM products 
ORDER BY price;  -- Error!

-- ✅ Include column in SELECT
SELECT DISTINCT category, price FROM products 
ORDER BY price;

Summary

ORDER BY clause key points:

• ASC: Ascending order (default)
• DESC: Descending order
• Multiple columns: Sort by multiple criteria
• Expressions: Sort by calculated values
• NULL handling: NULLS FIRST / NULLS LAST
• Performance: Use indexes for sorted columns
• Combine with: LIMIT, DISTINCT, GROUP BY