PostgreSQL ORDER BY

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:

  1. department (A-Z)
  2. 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 CaseQuery
Basic SortingORDER BY column_name ASC/DESC;
Multiple ColumnsORDER BY col1 ASC, col2 DESC;
Expression SortingORDER BY (price * quantity) DESC;
Sorting by Column PositionORDER BY 2 DESC;
Sorting NULLsORDER BY col ASC NULLS LAST;
Top N Results (LIMIT)ORDER BY col DESC LIMIT 5;
Custom Order with CASEORDER BY CASE ... END;

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

Soeng Souy

Soeng Souy

Website that learns and reads, PHP, Framework Laravel, How to and download Admin template sample source code free.

Post a Comment

CAN FEEDBACK
close