Introduction to PostgreSQL ORDER BY
When we receive data from a table using a select query, it will return a row in an unspecified order. To sort this data using specified order, we need to use order by clause in the select query to retrieve data from a table; we have to use ASC or DESC order to sort data in the specified order; this clause is used to sort data in ascending and descending order in PostgreSQL. We can use one or more column in order by clause to sort data in ascending or descending order, but we make sure that this column is present in that table.
Syntax:
This clause specifies the sort order of data in a specific order.
SELECTcolumn-list (list of columns)
FROM table_name (name of table)
[WHERE condition]
[ORDER BY column1, column2, …, columnN (name of columns)] [ASC | DESC];
SELECT* (list of columns)
FROM table_name (name of table from we retrieving data)
[WHERE condition]
[ORDER BY column1, column2, …, columnN (name of columns)] [ASC | DESC];
Select expressions (column list)
From table_name (name of table)
[WHERE condition]
[ORDER BY expression (name of column) [ASC | DESC | Using operator] [NULLS FIRST | NULLS LAST];
Below are the parameter description of the above syntax are as follows:
- Column list: List of columns name from which we have retrieving data.
- Asterisk (*): All columns from the table that we have retrieving data.
- Table name: This specifies the table name from which we have retrieving data. There must be at least one name we have to mention in from clause.
- Where condition: Where specifies that the specific condition needs to be fulfilled to retrieving data from the table. Where the condition is optional in order by clause.
- ASC (Ascending): This is an optional clause in PostgreSQL order by clause. It will sort the result by ascending order.
- DESC (Descending): This is an optional clause in PostgreSQL order by clause. It will sort the result by descending order.
- Nulls first: It will sort all null values before non-nulls in PostgreSQL order by clause result set. This is an optional clause in PostgreSQL order by clause.
- Nulls last: It will sort all null values after non-nulls in PostgreSQL order by clause result set. This is an optional clause.
How ORDER BY Function works in PostgreSQL?
- Normally without using order by function in PostgreSQL, data retrieve in an unspecified order.
- Using order by function in PostgreSQL, our data comes in the specified order. We have used the ASC function to sort data in ascending order.
- Also, the DESC function is available in PostgreSQL to sort data in descending order.
- We have used the column name in the order by clause to sort data in our query’s specified order.
- We have also used multiple column names in a single query to fetch sorted data from the table.
- This clause is also used without specifying the ASC and DESC function in our query. Suppose we have not provided ASC and DESC function in order by clause our result sorted by using ascending order.
- The default sequence is ascending order that will return the smallest value first. If suppose in some situations two rows are equal, they compare with each other using the expressions. If rows are the same by using the expression, then the output of the result set depends on the implementation of expressions.
- In this clause, it is not mandatory that add ASC or DESC after any expressions.
- In this clause, null value sorts will higher than the other value sorts.
- In this clause, two operators are used with the help of order by clause with using the keyword to retrieve the result set in ascending or descending order.
Example on Order By In PostgreSQL
We have used the employee table to describe an example of the order by clause in PostgreSQL. Please find below an example of the order by clause in PostgreSQL.
Example #1
Create an employee table to describe order by clause in PostgreSQL.
Code:
CREATE TABLE Employee ( emp_id INT NOT NULL, emp_name character(10) NOT NULL, emp_address character(20) NOT NULL, emp_phone character(14), PRIMARY KEY (emp_id));
Output:
Code:
INSERT INTO Employee (emp_id, emp_name, emp_address, emp_phone) VALUES (1, 'ABC', 'Pune', '1234567890');
INSERT INTO Employee (emp_id, emp_name, emp_address, emp_phone) VALUES (2, 'PQR', 'Pune', '1234567890');
INSERT INTO Employee (emp_id, emp_name, emp_address, emp_phone) VALUES (3, 'XYZ', 'Mumbai', '1234567890');
INSERT INTO Employee (emp_id, emp_name, emp_address, emp_phone) VALUES (4, 'BBS', 'Mumbai', '1234567890');
INSERT INTO Employee (emp_id, emp_name, emp_address, emp_phone) VALUES (5, 'RBS', 'Delhi', '1234567890');
Output:
Example #2
Order By Clause to sort rows with a single column.
Code:
SELECT emp_id, emp_name, emp_address from employee order by emp_name ASC;
Output:
Code:
SELECT emp_id, emp_name, emp_address from employee order by emp_name DESC;
Output:
Example #3
Order By Clause to sort rows with multiple columns.
Code:
SELECT emp_id, emp_name, emp_address from employee order by emp_name ASC, emp_id DESC;
Output:
SELECT emp_id, emp_name, emp_address from employee order by emp_name DESC, emp_id ASC;
Output:
Example #4
Order By Clause to sort rows with expressions.
Code:
SELECT emp_id, LENGTH(emp_address) emp_address, emp_name from employee order by length(emp_address) DESC;
Output:
Code:
SELECT emp_id, LENGTH(emp_address) emp_address, emp_name from employee order by length(emp_address) ASC;
Output:
Example #5
PostgreSQL order by clause to sort rows with using clause.
Code:
SELECT emp_id, emp_name, emp_address from employee order by emp_id USING>;
Output:
Code:
SELECT emp_id, emp_name, emp_address from employee order by emp_id USING<;
Output:
Conclusion
PostgreSQL ORDER BY clause sorting is based on ascending or descending order. We can use single or multiple columns in the query to retrieve data in sorted order. This clause will retrieve data in ascending order by default if we have not defined any sorting function in a query.
Recommended Articles
This has been a guide to PostgreSQL ORDER BY. Here we discuss the introduction and how the ORDER BY function works in PostgreSQL. You may also have a look at the following articles to learn more –
