In this post, we are going to learn how to sort the result set returned from the SELECTstatement by using the PostgreSQL ORDER BY clause.
PostgreSQL Order by clause:
If you want to sort the rows by some kind of attribute, you can use the SQL ORDER BY keyword.
- The ORDER BY keyword is used to sort the results in either ascending or descending order.
- The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword
Syntax
|
1 2 3 4 5 6 7 |
SELECT column_name, column_name FROM tbl_name ORDER BY { column-Name | ColumnPosition | Expression } [ ASC | DESC ] |
column-Name
Refers to the names visible from the SelectItems in the underlying query of the SELECT statement. The column-Name that you specify in the ORDER BY clause does not need to be the SELECT list.
ColumnPosition
An integer that identifies the number of the column in the SelectItems in the underlying query of the SELECT statement. ColumnPosition must be greater than 0 and not greater than the number of columns in the result table. In other words, if you want to order by a column, that column must be specified in the SELECT list.
Expression
A sort key expression, such as numeric, string, and datetime expressions. Expression can also be a row value expression such as a scalar subquery or case expression.
ASC
Specifies that the results should be returned in ascending order. If the order is not specified, ASC is the default.
DESC
Specifies that the results should be returned in descending order.
Example.,
|
1 2 3 4 5 6 7 8 9 10 |
testdb=> select * from emp where job='SALESMAN' order by empno desc; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+--------+----------+------+---------------------------+---------+---------+-------- 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00+05:30 | 1500.00 | 4.00 | 30 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00+05:30 | 1250.00 | 1400.00 | 30 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00+05:30 | 1250.00 | 500.00 | 30 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00+05:30 | 1600.00 | 300.00 | 30 (4 rows) testdb=> |
Here,
First, specify the column that you want to sort in the ORDER BY clause. If you sort the result set based on multiple columns, use a comma to separate between two columns.
Second, use ASC to sort the result set in ascending order and DESC to sort the result set in descending order. If you leave it blank, the ORDER BY clause will use ASC by default.
Words from postgreshelp
Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.
If you want to be updated with all our articles
please follow us on Facebook | Twitter
Please subscribe to our newsletter.
