PostgreSQL ORDER BY

PostgreSQL `ORDER BY` Clause

The ORDER BY clause in PostgreSQL is used to sort query results in either ascending (ASC) or descending (DESC) order.

1. Basic Syntax

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

ASC (default) → Sorts in ascending order (smallest to largest).
DESC → Sorts in descending order (largest to smallest).
Multiple columns can be used for sorting.

2. Sorting by One Column

Example: Get all employees sorted by salary (lowest to highest).

SELECT id, name, salary
FROM employees
ORDER BY salary;

🔹 Default is ASC, so it sorts from lowest to highest.

Descending Order

To get the highest salary first:

SELECT id, name, salary
FROM employees
ORDER BY salary DESC;

3. Sorting by Multiple Columns

Example: Sort employees first by department (A-Z), then by salary (highest to lowest).

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

🔹 Sorting precedence:

department (A-Z)
salary (highest to lowest) within each department

4. Sorting by an Expression

You can sort by calculated values, such as total price (price * quantity).

SELECT id, product, price, quantity, (price * quantity) AS total_price
FROM sales
ORDER BY total_price DESC;

🔹 Orders results by total_price in descending order.

5. Sorting by Column Position

Instead of column names, you can use column positions (starting from 1).

SELECT id, name, salary
FROM employees
ORDER BY 3 DESC; -- Sort by the 3rd column (salary)

🔹 Not recommended (column order changes may break the query).

6. Sorting NULL Values

By default:

NULLS FIRST → Used for ascending (ASC) order.
NULLS LAST → Used for descending (DESC) order.

Example: Sort products by price, but show NULL prices last.

SELECT id, product, price
FROM products
ORDER BY price ASC NULLS LAST;

Example: Show NULL values first in descending order.

SELECT id, product, price
FROM products
ORDER BY price DESC NULLS FIRST;

7. Using `ORDER BY` with `LIMIT`

To get top 5 highest-paid employees:

SELECT id, name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

🔹 Common for pagination and ranking queries.

8. Using `ORDER BY` with `DISTINCT`

PostgreSQL allows sorting even with DISTINCT values.

SELECT DISTINCT department
FROM employees
ORDER BY department;

🔹 Ensures unique values while sorting.

9. Using `ORDER BY` with `CASE` (Custom Sorting)

Sort employees:

Managers first
Developers second
Interns last

SELECT id, name, role
FROM employees
ORDER BY 
  CASE 
    WHEN role = 'Manager' THEN 1
    WHEN role = 'Developer' THEN 2
    WHEN role = 'Intern' THEN 3
    ELSE 4 
  END;

🔹 Custom sorting using CASE.

10. `ORDER BY` with Indexing for Performance

If your table is large, an index on the sorted column can speed up sorting.

CREATE INDEX idx_salary ON employees(salary);

🔹 Indexing improves sorting speed significantly.

Summary

Use Case	Query
Basic Sorting	`ORDER BY column_name ASC/DESC;`
Multiple Columns	`ORDER BY col1 ASC, col2 DESC;`
Expression Sorting	`ORDER BY (price * quantity) DESC;`
Sorting by Column Position	`ORDER BY 2 DESC;`
Sorting NULLs	`ORDER BY col ASC NULLS LAST;`
Top N Results (`LIMIT`)	`ORDER BY col DESC LIMIT 5;`
Custom Order with `CASE`	`ORDER BY CASE ... END;`

Would you like examples based on your specific database structure? 🚀

Souy Soeng

PostgreSQL ORDER BY