PostgreSQL ORDER BY

The ORDER BY clause simply allows you to return records in a particular order. In general, PostgreSQL returns the rows in the order that they were inserted into the table. If you need to records in a particular order you must use the ORDER BY clause. Below is an example:

SELECT *
FROM employee
ORDER BY hire_date;

+----+------------+-----------+-------------+---------+------------+
| id | first_name | last_name |    dept     | salary  | hire_date  |
+----+------------+-----------+-------------+---------+------------+
|  1 | Joe        | Jones     | Sales       | 1034.23 | 2015-01-15 |
|  2 | Sally      | Smith     | Sales       | 1195.98 | 2015-01-15 |
|  6 | Troy       | Anderson  | Sales       | 1158.34 | 2017-04-15 |
|  4 | Gunner     | Greenburg | Engineering | 2169.32 | 2017-09-22 |
|  5 | Jack       | Doe       | Finance     | 1945.42 | 2018-06-20 |
|  3 | Lisa       | Teal      | Engineering | 2496.78 | 2019-03-10 |
+----+------------+-----------+-------------+---------+------------+

Here you can see we are sorting the records by the hire date. If you want to see the records sorted with the newest record first simple add DESC to your ORDER BY clause. (the default is ASC)

SELECT *
FROM employee
ORDER BY hire_date DESC;

+----+------------+-----------+-------------+---------+------------+
| id | first_name | last_name |    dept     | salary  | hire_date  |
+----+------------+-----------+-------------+---------+------------+
|  3 | Lisa       | Teal      | Engineering | 2496.78 | 2019-03-10 |
|  5 | Jack       | Doe       | Finance     | 1945.42 | 2018-06-20 |
|  4 | Gunner     | Greenburg | Engineering | 2169.32 | 2017-09-22 |
|  6 | Troy       | Anderson  | Sales       | 1158.34 | 2017-04-15 |
|  1 | Joe        | Jones     | Sales       | 1034.23 | 2015-01-15 |
|  2 | Sally      | Smith     | Sales       | 1195.98 | 2015-01-15 |
+----+------------+-----------+-------------+---------+------------+

In the above example, you can see the records are sorted in descending order. You can also sort on multiple columns. In the below example we want to order our records by the hire date, but within each department.

SELECT *
FROM employee
ORDER BY dept, hire_date;

+----+------------+-----------+-------------+---------+------------+
| id | first_name | last_name |    dept     | salary  | hire_date  |
+----+------------+-----------+-------------+---------+------------+
|  4 | Gunner     | Greenburg | Engineering | 2169.32 | 2017-09-22 |
|  3 | Lisa       | Teal      | Engineering | 2496.78 | 2019-03-10 |
|  5 | Jack       | Doe       | Finance     | 1945.42 | 2018-06-20 |
|  1 | Joe        | Jones     | Sales       | 1034.23 | 2015-01-15 |
|  2 | Sally      | Smith     | Sales       | 1195.98 | 2015-01-15 |
|  6 | Troy       | Anderson  | Sales       | 1158.34 | 2017-04-15 |
+----+------------+-----------+-------------+---------+------------+

The SQL standard only allows sorting rows based on the columns that also appear in the SELECT clause. PostgreSQL, however, allows you to sort rows based on columns that do not appear in the selection list. In general, we recommend not doing this as it makes your SQL less portable to another database platform.

Next: Select WHERE Clause