ORDER BY in PostgreSQL

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

1. Sorting in Ascending Order (Default)

SELECT * FROM employees ORDER BY salary;

✅ Returns all employees sorted by salary in ascending order (lowest to highest).

2. Sorting in Descending Order

SELECT * FROM employees ORDER BY salary DESC;

✅ Returns employees sorted by salary in descending order (highest to lowest).

3. Sorting by Multiple Columns

SELECT * FROM employees ORDER BY department ASC, salary DESC;

✅ Sorts results:

• First by department (A → Z).
• Then, within each department, by salary (highest → lowest).

4. Sorting by Date

SELECT * FROM orders ORDER BY order_date DESC;

✅ Returns the most recent orders first.

5. Using ORDER BY with LIMIT

SELECT * FROM employees ORDER BY salary DESC LIMIT 5;

✅ Returns the top 5 highest-paid employees.

6. Using ORDER BY with NULLS FIRST or NULLS LAST

• Default behavior:
  • ASC → NULL values first.
  • DESC → NULL values last.

SELECT * FROM employees ORDER BY salary DESC NULLS FIRST;

✅ Places NULL salaries first, then sorts non-null salaries in descending order.

SELECT * FROM employees ORDER BY salary ASC NULLS LAST;

✅ Places NULL salaries last, then sorts non-null salaries in ascending order.

7. Using ORDER BY on a Computed Column

SELECT *, salary * 12 AS annual_salary FROM employees ORDER BY annual_salary DESC;

✅ Sorts employees by annual salary (calculated as salary * 12).

Performance Tip

• Indexes can improve sorting speed, especially on large tables.