How to sort rows using ORDER BY and SELECT in PostgreSQL: 6 Examples

13 Agent

Arun Gavhane Senior Systems Engineer

SUMMARY: This article reviews five ways to sort table data using the ORDER BY clause together with the SELECT statement in PostgreSQL.

1. Sorting by column in ascending order

2. Sorting by column in descending order

3. Sorting by multiple columns

4. Sorting using arithmetic operators

5. Sorting by column alias

6. Sorting with GROUP BY and ORDER BY

 

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)

 

 

Arun GavhaneSenior Systems Engineer