Definition of PostgreSQL Sort
PostgreSql provides a sorting function to users. Sorting function plays an important role in database management systems. With the help of sorting function, we can arrange data as per our requirement such as ascending or descending order. We can apply sort functions on single columns or multiple columns by using group by and order by clause. Before execution of the sort function, we must need a table with records. For the execution purpose of the sort function we use order by clause in the select statement to sort table records. We can also implement sort functions with different arithmetic operators.
Syntax:
select column named1, column named2…… column namedN from table name order by column name1, column name2,…………. column nameN;
Explanation: In above syntax we use order by clause in select statement where column named1, column named2 and column namedn is used to display column in table where table name means specified table name in database and column name1, column name2 and column namen is used to apply order by clause.
How Sort Function Works in PostgreSQL
- We must install PostgreSql in your system.
- We required basic knowledge about PostgreSql.
- We must require tables to perform Sort function.
- We must need basic knowledge about order by clause that means how it is used.
Examples to Implement Sort Function in PostgreSQL
Different ways to implement sort function as below
Basically sorting technique we use order by clause to sort the record from database table, so first we create table by using following statement.
create table company (emp_id int primary key not null,
emp_name text not null, emp_age int not null,
emp_salary int not null);
Illustrate the end result of the above declaration by using the use of the following snapshot.
1. Sort by Ascending Order
In this method we sort records in the database table in ascending order. For more details see the following example.
Example:
Select emp_id, emp_name, emp_salary from company order by
emp_salary asc;
Explanation: In the above example, suppose we need to sort employee salary in ascending order, at that time use the above statement to sort employee salary. Illustrate the end result of the above declaration by using the use of the following snapshot.
2. Sort by Descending Order
In this method, we sort records of the database table in descending order. For more details see the following example.
Example:
select emp_id, emp_name, emp_salary from company order by emp_salary desc;
Explanation: In the above example, suppose we need to sort employee salary in descending order. At that time we use the above statement to sort employee salary. Illustrate the end result of the above declaration by using the use of the following snapshot.
3. Sort by Multiple Column Ascending Order
In this method we sort multiple columns in ascending order by using the following statement.
Example:
select emp_id, emp_age, emp_name, emp_salary from company order by emp_age, emp_salary asc;
Explanation: In the above example, suppose we need to sort multiple columns in ascending order, in this example we sort emp_age and emp_salary in ascending order by using the above statement. Illustrate the end result of the above declaration by using the use of the following snapshot.
4. Sort by Multiple Column Descending Order
In this method we sort one or more columns in descending order of the database table. For details see the following example.
Example:
select emp_name, emp_age, emp_salary from company order by emp_id, emp_salary desc;
Explanation: In the above example, suppose we need to sort emp_id and emp_salary in descending order by using the above statement. Illustrate the end result of the above declaration by using the use of the following snapshot.
5. Sort Column by Using Arithmetic Operators
In this method we sort columns of the database table by using arithmetic operators on specified columns.
Example:
select emp_id, emp_salary+200 from company order by emp_salary+200 asc;
Explanation: In this example, suppose we need to add Rs. 200 in each employee and need to order in ascending order at that time we use the above statement. Illustrate the end result of the above declaration by using the use of the following snapshot.
6. Sort by Using Column Alias
In this method we sort records from the database table using alias. For more details see the following example.
select emp_name, emp_salary*12 "Annual Salary" from company order by "Annual Salary";
Explanation: In the above example, suppose we need to calculate the annual salary of an employee and we need to arrange them in ascending order using alias. By default order of order by clause is ascending order. Illustrate the end result of the above declaration by using the use of the following snapshot.
In the same example we arrange the record of the database table in descending order by using the following statement.
select emp_name, emp_salary*12 "Annual Salary" from company order by "Annual Salary" desc;
Explanation: In the above example we need to arrange employee annual salary by using aliases and need to arrange in the descending order. See here we also use an arithmetic operator. Illustrate the end result of the above declaration by using the use of the following snapshot.
7. Sort by Using Group by And Order By
In this method we arrange records by using Group by and Order by clause for details see following example.
Example:
select emp_dept, max(emp_salary) from company group by emp_dept order by max(emp_salary) ;
Explanation: In the above example, suppose we need to calculate the max salary of an employee in each department by using group by and order by clause at that time we use the above statement. In this example max salary of IT dept is 28000, max salary of MECH dept is 30000 and max salary of CIVIL dept is 40000.Illustrate the end result of the above declaration by using the use of the following snapshot.
Conclusion
We hope from this article you have understood about the PostgreSQL Sort statement. From the above article, we learn basic syntax of sort such as order by clause and we also learn how we can implement sort function using different methods with group by clause. From this article, we learn how we can handle sorting tables of databases.
Recommended Articles
This is a guide to PostgreSQL Sort. Here we also discuss the definition and how sort function works in postgresql along with different examples and its code implementation. You may also have a look at the following articles to learn more –
