PostgreSQL – ORDER BY Clause

An ‘ORDER BY’ clause is used in the select statement to sort the result set in either ascending or descending order. The default sorting order is ‘ascending’.

Syntax:

ORDER BY column_name [asc,desc];

Example:

Consider the employee table as shown below:

id	first_name	last_name	department	salary
101	bryan	walker	sales	50,000
102	alex	carpenter	accounts	55,000
103	sam	white	sales	60,000
104	john	barry	sales	54,000
105	bill	wood	accounts	65,000
106	robert	hopkins	support	70,000
107	david	lee	support	72,000
108	jack	sears	support	65,000
109	tom	young	sales	55,000

Below is the SQL query to return data from the employee table sorted by their salaries.

SELECT * FROM employee ORDER BY salary;

id first_name last_name department salary
--- --------- --------- ---------- -------
101 bryan      walker    sales      50000
104 john       barry     sales      54000
102 alex       carpenter accounts   55000
109 tom        young     sales      55000
.
.
.
.

As you can see, all employees are sorted by the salary column from least to highest. In our example, both Alex and Tom have same salary. In such scenarios, either ‘Alex’ might appear before ‘Tom’ or vice-versa. The result is unpredictable. To have more control on the ordering, you can specify another column in the ORDER BY clause for second layer of sorting as shown below.

SELECT * FROM employee ORDER BY salary, first_name;

id first_name last_name department salary
--- --------- --------- ---------- -------
101 bryan      walker    sales      50000
104 john       barry     sales      54000
102 alex       carpenter accounts   55000
109 tom        young     sales      55000
.
.
.

By adding first_name to the ORDER BY clause, it is guaranteed that ‘Alex’ will always appear before ‘Tom’.

To sort the data in the descending order, simply specify ‘DESC’ after the column name within ORDER BY clause.

SELECT * FROM employee ORDER BY salary DESC, first_name;

id first_name last_name department salary
--- --------- --------- ---------- -------
107 david      lee       support    72000
106 robert     hopkins   support    70000
105 bill       wood      accounts   65000
108 jack       sears     support    65000
.
.
.
.

You can also specify column positions instead of column names in the ORDER BY clause.

SELECT * FROM employee ORDER BY salary; 

SELECT * FROM employee ORDER BY 5; 

Both the above queries give the same result set.