The ORDER BY clause in PostgreSQL is used together with the SELECT statement to sort table data. The table data can either be sorted in ascending or descending order. By default, the data is sorted in ascending order.
1. Sorting by column in ascending order
Here we use ORDER BY to sort the “hiredate” column in asc (ascending) order:
postgres=# select empno,ename,hiredate from emp order by hiredate asc;
empno | ename | hiredate
-------+--------+---------------------
7369 | SMITH | 1980-12-17 00:00:00
7499 | ALLEN | 1981-02-20 00:00:00
7521 | WARD | 1981-02-22 00:00:00
7566 | JONES | 1981-04-02 00:00:00
7698 | BLAKE | 1981-05-01 00:00:00
7782 | CLARK | 1981-06-09 00:00:00
7844 | TURNER | 1981-09-08 00:00:00
7654 | MARTIN | 1981-09-28 00:00:00
7839 | KING | 1981-11-17 00:00:00
7902 | FORD | 1981-12-03 00:00:00
7900 | JAMES | 1981-12-03 00:00:00
7934 | MILLER | 1982-01-23 00:00:00
7788 | SCOTT | 1987-04-19 00:00:00
7876 | ADAMS | 1987-05-23 00:00:00
(14 rows)
2. Sorting by column in descending order
Here we use ORDER BY to sort the “hiredate” column in desc (descending) order:
postgres=# select empno,ename,hiredate from emp order by hiredate desc;
empno | ename | hiredate
-------+--------+---------------------
7876 | ADAMS | 1987-05-23 00:00:00
7788 | SCOTT | 1987-04-19 00:00:00
7934 | MILLER | 1982-01-23 00:00:00
7900 | JAMES | 1981-12-03 00:00:00
7902 | FORD | 1981-12-03 00:00:00
7839 | KING | 1981-11-17 00:00:00
7654 | MARTIN | 1981-09-28 00:00:00
7844 | TURNER | 1981-09-08 00:00:00
7782 | CLARK | 1981-06-09 00:00:00
7698 | BLAKE | 1981-05-01 00:00:00
7566 | JONES | 1981-04-02 00:00:00
7521 | WARD | 1981-02-22 00:00:00
7499 | ALLEN | 1981-02-20 00:00:00
7369 | SMITH | 1980-12-17 00:00:00
(14 rows)
3. Sorting by multiple columns
Here the table data is sorted based on more than column. In the ORDER BY clause, the first column listed is “deptno,” where the data will be first sorted according to the value in “deptno” in asc order. Then the data will be sorted by the second column “sal” in descending order (since here we specified desc), with respect to the values in the first column “deptno”:
postgres=# select empno, ename, deptno,sal from emp order by deptno,sal desc;
empno | ename | deptno | sal
-------+--------+--------+---------
7839 | KING | 10 | 5000.00
7782 | CLARK | 10 | 2450.00
7934 | MILLER | 10 | 1300.00
7788 | SCOTT | 20 | 3000.00
7902 | FORD | 20 | 3000.00
7566 | JONES | 20 | 2975.00
7876 | ADAMS | 20 | 1100.00
7369 | SMITH | 20 | 800.00
7698 | BLAKE | 30 | 2850.00
7499 | ALLEN | 30 | 1600.00
7844 | TURNER | 30 | 1500.00
7521 | WARD | 30 | 1250.00
7654 | MARTIN | 30 | 1250.00
7900 | JAMES | 30 | 950.00
(14 rows)
4. Sorting using arithmetic operators
Arithmetic operators can be added to the values in the columns specified in the ORDER BY clause (here, we add +100 to the “sal” column):
postgres=# select empno,ename,sal+100 from emp order by sal+100;
empno | ename | ?column?
-------+--------+----------
7369 | SMITH | 900.00
7900 | JAMES | 1050.00
7876 | ADAMS | 1200.00
7521 | WARD | 1350.00
7654 | MARTIN | 1350.00
7934 | MILLER | 1400.00
7844 | TURNER | 1600.00
7499 | ALLEN | 1700.00
7782 | CLARK | 2550.00
7698 | BLAKE | 2950.00
7566 | JONES | 3075.00
7902 | FORD | 3100.00
7788 | SCOTT | 3100.00
7839 | KING | 5100.00
(14 rows)
5. Sorting the table data by column alias
Data can also be sorted by specifying the column alias name in the ORDER BY clause for a column alias named in the SELECT statement:
postgres=# select empno,ename,sal*12 "Annual Salary" from emp order by "Annual Salary";
empno | ename | Annual Salary
-------+--------+---------------
7369 | SMITH | 9600.00
7900 | JAMES | 11400.00
7876 | ADAMS | 13200.00
7521 | WARD | 15000.00
7654 | MARTIN | 15000.00
7934 | MILLER | 15600.00
7844 | TURNER | 18000.00
7499 | ALLEN | 19200.00
7782 | CLARK | 29400.00
7698 | BLAKE | 34200.00
7566 | JONES | 35700.00
7902 | FORD | 36000.00
7788 | SCOTT | 36000.00
7839 | KING | 60000.00
(14 rows)
postgres=# select empno,ename,sal*12 annualsalary from emp order by annualsalary;
empno | ename | annualsalary
-------+--------+--------------
7369 | SMITH | 9600.00
7900 | JAMES | 11400.00
7876 | ADAMS | 13200.00
7521 | WARD | 15000.00
7654 | MARTIN | 15000.00
7934 | MILLER | 15600.00
7844 | TURNER | 18000.00
7499 | ALLEN | 19200.00
7782 | CLARK | 29400.00
7698 | BLAKE | 34200.00
7566 | JONES | 35700.00
7902 | FORD | 36000.00
7788 | SCOTT | 36000.00
7839 | KING | 60000.00
(14 rows)
6. Sorting with GROUP BY and ORDER BY
Data can be sorted with the ORDER BY clause in a SELECT statement that also uses the GROUP BY function:
postgres=# select deptno,max(sal) from emp group by deptno order by max(sal);
deptno | max
--------+---------
30 | 2850.00
20 | 3000.00
10 | 5000.00
(3 rows)