Sorting Data According to More Than One Column

PostgreSQL is an advanced relational database system with the ability to switch to both relational (SQL) and non-relational (JSON) queries. It is free and open-source. Things become overwhelming when you want to sort a table with more than one column, PostgreSQL which is an open-source relational database and has strong multi-column sorting will help you with these many columns. In this article, we will discuss how PostgreSQL handles the sorting of data based on more than one column.

Sorting Data According to More Than One Column

In PostgreSQL, the multi-column sorting is provided by the ORDER BY clause inserted into the SELECT statement. As for the ORDER BY clause it can sort the resulting set of records either in ascending or descending order. It sorts the records by default in ascending order. The syntax is as follows:

Syntax:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

Here, column1, column2, etc., represent the columns by which the data should be sorted.
The optional ASC (ascending) or DESC (descending) keywords determine the sorting order for each column.

Example Queries of sorting data to more than one column

Now let’s create a table and insert some data into it and then we will perform some queries onto that table.

Create employees table

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary NUMERIC(10, 2),
    hire_date DATE
);

Insert sample data into employees table

INSERT INTO employees (first_name, last_name, department, salary, hire_date)
VALUES 
    ('Minal', 'Pandey', 'HR', 50000.00, '2020-01-15'),
    ('Mahi', 'Pandey', 'IT', 60000.00, '2019-05-20'),
    ('Soni', 'Pandey', 'HR', 55000.00, '2018-08-10'),
    ('Abhilekh', 'Pandey', 'Finance', 70000.00, '2021-02-28'),
    ('Sudarshan', 'Pandey', 'IT', 65000.00, '2020-11-10');

Output:

You can see the content of the table by executing the below command:

SELECT * FROM employees;

Employees Table

Examples of Sorting Data According to More Than One Column

Example 1: Sorting by Two Columns

Let’s sort the employees table first by department (ascending) and then by salary (descending).

SELECT first_name, last_name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

Explanation:

SELECT first_name, last_name, department, salary: It specifies that the columns to be retrieved (first_name, last_name, department, salary) from the employees table.
FROM employees: Specifies the table from which the data is retrieved.
ORDER BY department ASC, salary DESC: Defines the sorting order for the result set. It first sorts the rows by the department column in ascending order (ASC). If two rows have the same value in the department column, the ORDER BY clause further sorts those rows by the salary column in descending order (DESC).

Output:

This query will display the employees sorted by department in ascending order. Within each department, employees will be sorted by salary in descending order.

Sorting by Two Columns

Example 2: Sorting by Three Columns.

Now, let’s sort the employees table by department (ascending), salary (descending), and then hire_date (ascending).

SELECT first_name, last_name, department, salary, hire_date
FROM employees
ORDER BY department ASC, salary DESC, hire_date ASC;

Explanation:

SELECT first_name, last_name, department, salary, hire_date: It specifies that the columns to be retrieved (first_name, last_name, department, salary, hire_date) from the employees table.
FROM employees: Specifies the table from which the data is retrieved.
ORDER BY department ASC, salary DESC, hire_date ASC: Defines the sorting order for the result set based on multiple columns. It first sorts the rows by the department column in ascending order (ASC). Within each department, it sorts the rows by the salary column in descending order (DESC). If two rows have the same department and salary, they are further sorted by the hire_date column in ascending order (ASC).

Output:

This query will arrange the employees first by department in ascending order. Within each department, employees will be sorted by salary in descending order. For employees with the same department and salary, they will be further sorted by hire date in ascending order.

Sorting by Three Columns

Conclusion

PostgreSQL users are equipped with multi-column sorting skills in order to organize data by multiple criteria. With the ORDER BY clause, and its combination with suitable columns, complex sorting requests can be easily carried out. Whether that be sorting two columns or more, understanding and implementing multi-column sorting strengthens the analytical abilities of PostgreSQL, thus allowing better data retrieval and analysis.

Article Tags :

Databases

PostgreSQL