WikiGalaxy
PostgreSQL Order By Clause
Purpose of ORDER BY:
The ORDER BY clause in PostgreSQL is used to sort the result set of a query by one or more columns. By default, it sorts in ascending order.
Sorting in Ascending Order:
To sort in ascending order, specify the column name followed by ASC. Since ascending order is the default, ASC can be omitted.
Sorting in Descending Order:
To sort in descending order, use the DESC keyword after the column name.
Multiple Column Sorting:
You can sort by multiple columns by listing them separated by commas. Each column can have its own sort direction.
Nulls First/Last:
PostgreSQL allows you to specify whether NULL values should appear first or last using NULLS FIRST or NULLS LAST.
Performance Considerations:
Using ORDER BY can impact performance, especially on large datasets. Proper indexing can help mitigate this.
SELECT * FROM employees ORDER BY last_name ASC;
Example Explanation:
This query retrieves all records from the "employees" table and sorts them by "last_name" in ascending order.
Console Output:
John Doe, Jane Smith, Robert Brown
Using ORDER BY with Multiple Columns
Complex Sorting:
You can sort by multiple columns to create a more complex sorting logic. For instance, sort by department and within each department, sort by employee name.
Ascending and Descending Mix:
Each column can have its own sorting direction. The first column could be ascending, while the second one could be descending.
SELECT * FROM employees ORDER BY department ASC, salary DESC;
Example Explanation:
This query sorts the "employees" table by "department" in ascending order and then by "salary" in descending order within each department.
Console Output:
HR: Alice (60000), Bob (55000); IT: Charlie (70000), Dave (65000)
ORDER BY with NULLS FIRST
Handling NULL Values:
NULL values can be sorted explicitly to appear first or last. This is useful when NULLs have a specific meaning in your dataset.
NULLS FIRST Option:
Use NULLS FIRST to make NULL values appear at the beginning of your sorted result set.
SELECT * FROM products ORDER BY price ASC NULLS FIRST;
Example Explanation:
This query sorts the "products" table by "price" in ascending order, placing NULL values first.
Console Output:
NULL, 10.99, 15.99, 20.00
ORDER BY with NULLS LAST
NULLS LAST Option:
Use NULLS LAST to ensure that NULL values appear at the end of your sorted result set.
SELECT * FROM products ORDER BY price DESC NULLS LAST;
Example Explanation:
This query sorts the "products" table by "price" in descending order, placing NULL values last.
Console Output:
20.00, 15.99, 10.99, NULL
ORDER BY with Expressions
Using Expressions:
You can use expressions in the ORDER BY clause to sort data based on calculated values.
SELECT *, (price * quantity) AS total_value FROM products ORDER BY total_value DESC;
Example Explanation:
This query calculates the "total_value" for each product and sorts the results in descending order based on this calculated value.
Console Output:
$200.00, $150.00, $100.00
ORDER BY with Aliases
Using Aliases:
Aliases can be used in the ORDER BY clause to refer to computed columns.
SELECT name, (price * quantity) AS total_value FROM products ORDER BY total_value DESC;
Example Explanation:
This query uses the alias "total_value" for a calculated column and sorts the results by this alias in descending order.
Console Output:
Product A: $200.00, Product B: $150.00
ORDER BY with LIMIT
Limiting Results:
The ORDER BY clause can be combined with the LIMIT clause to return a specified number of sorted results.
SELECT * FROM employees ORDER BY hire_date DESC LIMIT 5;
Example Explanation:
This query retrieves the most recently hired five employees from the "employees" table.
Console Output:
Employee A, Employee B, Employee C, Employee D, Employee E
ORDER BY with Subqueries
Subquery Sorting:
ORDER BY can be applied to subqueries to sort the results of the subquery before they are used by the outer query.
SELECT * FROM (SELECT * FROM employees ORDER BY salary DESC) AS sorted_employees LIMIT 3;
Example Explanation:
This query uses a subquery to sort employees by salary in descending order and then selects the top three results.
Console Output:
High Salary Employee 1, High Salary Employee 2, High Salary Employee 3
