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)
Popular Links
Featured Links
Relevant Blogs
EDB Tutorial: How to run a TPC-E-like benchmark easily
The TPC Benchmark(TM) E (TPC-E) was released back in 2007 as the successor to the TPC Benchmark(TM) C (TPC-C) for an OLTP type workload. To briefly recap, the TPC-E models...EDB Tutorial: How to Use Privilege Analysis to Identify [Un]Used Privileges
Have you ever searched for unnecessary granted privileges in your database? To find these, you need to know 1. The granted privileges 2. How many of the granted privileges have...EDB Tutorial: How To Get the Most Out of EDB Query Advisor
Enterprise-class databases require DBAs who are responsible for maximizing query performance. Therefore, when it comes to large scale deployment of databases, minimizing the work of DBA is important. One of...More Blogs
EDB Tutorial: How to Configure Databases for EDB JDBC SSL Factory Classes
The fact is, SSL configuration required by EDB JDBC SSL factory classes is considered confusing and challenging by many EDB customers. This stems from the concepts behind the SSL handshake...