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
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).
🔹 Default is ASC
, so it sorts from lowest to highest.
Descending Order
To get the highest salary first:
3. Sorting by Multiple Columns
Example: Sort employees first by department (A-Z), then by salary (highest to lowest).
🔹 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
).
🔹 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
).
🔹 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.
Example: Show NULL
values first in descending order.
7. Using ORDER BY
with LIMIT
To get top 5 highest-paid employees:
🔹 Common for pagination and ranking queries.
8. Using ORDER BY
with DISTINCT
PostgreSQL allows sorting even with DISTINCT
values.
🔹 Ensures unique values while sorting.
9. Using ORDER BY
with CASE
(Custom Sorting)
Sort employees:
- Managers first
- Developers second
- Interns last
🔹 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.
🔹 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? 🚀