PostgreSQL ORDER BY

ORDER BY Clause

How the result set will appear from a table according to a select statement, either in the normal sequence of the table or in ascending or descending order on specific column(s), depending on the ORDER BY clause in PostgreSQL . More than one columns can be ordered by ORDER BY clause. The default sorting sequence is in ascending order that is smallest value comes first.

When two rows are equal according to the first expression then they will be compared by themselves by another expression and so on. If the rows are equal according to all specified expressions, then the return of result set depends on the implementation of expression.

It is optional that, one can add the keyword ASC or DESC after any expression in the ORDER BY clause to get the result set in ascending or descending order. If not specified, ASC is assumed by default. Two specific operators can be used along with the ORDER BY clause with USING keyword to make the result set ascending or descending. The ASC equivalent is USING < and DESC equivalent is USING >.

The NULL value sorts higher than the other value. When sort order is ascending the NULL value comes at the end and in the case of descending sort order, it comes at the beginning.

Syntax:

ORDER BY expression [ ASC | DESC | USING operator ] [, ...]

Parameters

PostgreSQL ORDER BY example1

The sample table

If we want to get the empno,emp_first_name,designame and salary by a sorting order on salary column from the employee table, the following SQL can be used.

SQL

Code:

SELECT empno,emp_first_name,designame,salary
FROM employee
ORDER BY salary;

Output:

PostgreSQL ORDER BY with WHERE clause

If we want to get the empno,emp_first_name,designame,deptno and salary by a sorting order on salary column from the employee table for that employee who belongs to the deptno 25, the following SQL can be used.

SQL

Code:

SELECT empno,emp_first_name,designame,deptno,salary
FROM employee
WHERE deptno=25
ORDER BY salary;

Output:

PostgreSQL ORDER BY with descending order

If we want to get the empno,emp_first_name,designame,deptno and salary by a sort in descending order on salary column from the employee table for that employee who belongs to the deptno 25, the following SQL can be used.

SQL

Code:

SELECT empno,emp_first_name,designame,deptno,salary
FROM employee
WHERE deptno=25
ORDER BY salary DESC;

The same above we can get by the following :

PostgreSQL ORDER BY with USING clause

Code:

SELECT empno,emp_first_name,designame,deptno,salary
FROM employee
WHERE deptno=25
ORDER BY salary USING>;

Here in the above example USING clause has been used and after this clause, the ( > ) operator sorts the results in descending order.

Output:

PostgreSQL ORDER BY on NULL values in descending order

If we want to get the emp_first_name,designame,commission and deptno by a sort in descending order on commission column from the employee table for that employee who belongs to the deptno 25, the following SQL can be used.

SQL

Code:

SELECT emp_first_name,designame,commission,deptno
FROM employee
WHERE deptno=25
ORDER BY commission USING>;

Output:

Explanation

The ORDER BY in the above example followed by USING> sorts the commission column in descending order. Here an important point is to be noted that, the NULL values in commission column comes first because we have already discussed that the NULL values is higher than the other values.

PostgreSQL ORDER BY with USING clause in ascending order

If we want to get the emp_first_name,designame,commission and deptno by a sort in ascending order on commission column from the employee table for that employee who belongs to the deptno 25, the following SQL can be used.

SQL

Code:

SELECT emp_first_name,designame,commission,deptno
FROM employee
WHERE deptno=25
ORDER BY commission USING<;

Output:

PostgreSQL ORDER BY multiple values in specific order

SQL

Code:

SELECT emp_first_name,designame,mngr_no,deptno,salary
FROM employee
ORDER BY designame,mngr_no,deptno asc,salary asc;

Output:

Explanation:

Here in the above output, the designame 'CLERCK' have come four times, within them, the mngr_no sorted in ascending order and three of them are same, within them deptno sorted in ascending order and two of them, are same and within them the salary sorted in ascending order. As the salary of the last level are same so no such effect has shown in the output.

The designame 'MANAGER' have come three times, within them, the mngr_no sorted in ascending order and all three of them are same, within them, deptno sorted in ascending order and two of them are same and within them, the salary sorted in ascending order. That is why the 21000 comes first.

The designame 'OFFICER' have come two times, within them, the mngr_no sorted in ascending order and as they are different so, the remaining level have shown no effect for this statement.

The designame 'SALESMAN' have come three times, within them, the mngr_no sorted in ascending order and two of them are same, within them, deptno sorted in ascending order and as they are different so, the remaining level have shown no effect for this statement.

Previous: GROUP BY
Next: HAVING