PostgreSQL: ORDER BY Clause

The SELECT statement returns rows in an unspecified order. Use the ORDER BY clause in the SELECT statement to get the data in ascending or descending order based on one or more columns.

SELECT <column_1>, <column_2>,...<column_N> 
FROM <table_name>
ORDER BY <column_name>|<sort_expression> [ASC | DESC];

As per the above syntax, specify column_name or sort_expression that you want the result set to be sorted by after ORDER BY keywords. After specifying a column or expression, you can give optional ASC or DESC keywords for sorting in ascending or descending order. If you do not specify ASC or DESC, by default PostgreSQL will sort the result in ascending order.

PostgreSQL evaluates the SQL query in the following order: FROM, WHERE, GROUP BY, HAVING, ORDER BY, and then SELECT.

Let's use the following Employee table to demonstrate the ORDER BY clause.

The following query will fetch all the records from the Employee table and sorts the result in ascending order of the first_name values.

SELECT * FROM employee
ORDER BY first_name;

The above query will return the following result:

The DESC will return rows in the descending order of the first_name value, as shown below.

Sort by Multiple Columns

In PostgreSQL, the ORDER BY clause can include multiple columns in different sorting orders (ascending or descending). When you include multiple columns with the ORDER BY clause, it will sort the records based on the first column, and if any two or more records have the same value in the first ORDER BY column, it will sort them by the second ORDER BY column.

SELECT * FROM employee
ORDER BY dept_id, first_name;

The above query will first sort the result by dept_id, and then the rows having the same dept_id will be sorted by the first_name. Remember, we have not included ASC or DESC, So it will sort the result in ascending order by default, as shown below.

Now let's run the same query with one column in ascending and another in descending order. The query will first sort the result by descending order of dept_id, and then the rows having the same dept_id will be sorted by ascending order of first_name.

SELECT * FROM employee
ORDER BY dept_id DESC, first_name;

Sort Group of Records

The following query sorts the group of records. It first group by dept_id, and then for each dept_id, it sort employees by descending order of dept_id.

SELECT dept_id AS "Department Id", COUNT(emp_id) AS "No of Employees"
FROM employee
GROUP BY dept_id
ORDER BY dept_id DESC;

Column alias can be used in the ORDER BY clause. The following query group by dept_id, where each dept_id gets many employees and sort by descending order of column alias emp_count.

SELECT dept_id, COUNT(emp_id) AS emp_count
FROM employee
GROUP BY dept_id
ORDER BY emp_count DESC;