Some data needs to be sorted in order to perform effective data analysis and determine the data trends. The ordering is to be done on some basis, by making use of the ORDER BY clause. The sorting order criteria are to be determined in the query to sort the specific data columns. This tutorial will help us deeply understand the workings of the ORDER BY clause, to sort the table data and some use cases of the ORDER BY clause. We will mainly go over the following content in this article:
- ORDER BY Clause PostgreSQL- How to Sort Table’s Data?
- Example 1: ORDER BY Clause to sort data in descending order PostgreSQL
- Example 2: Sorting Multiple columns using the ORDER BY.
- Example 3: Sorting data by Default ORDER BY Clause PostgreSQL
- Example 4: Limiting the sorted rows using the ORDER BY.
- Example 5: Sort data by date using the ORDER BY clause.
- Example 6: Using NULL with ORDER BY clause.
- Example 7: Using GROUP BY with ORDER BY clause.
- Example 8: Randomly sorting data rows using the ORDER BY.
Let’s go over all these methods one by one.
ORDER BY Clause PostgreSQL- How to Sort Table’s Data?
We can sort the data present in the Postgres table by utilizing the ORDER BY clause. The basic query/syntax for the ORDER BY clause can be written like this:
SELECT col_names
FROM tab_names
[WHERE conds]
[ORDER BY col_1, col_2, .. col_N] [ASC | DESC];
Syntax Description
- The SELECT statement follows the columns which we want to get in the output.
- The table’s name has to be written after the FROM clause, from which we want to get the sorted data.
- We can optionally determine any condition on the basis of which the sorting will take place. For this purpose, the WHERE clause is specified.
- The ORDER BY clause sorts the table’s data in the specified order.
- The order can be determined after the ORDER BY clause.
- We can also use ASC and DESC keywords to arrange the table data. Using these keywords table data gets arranged in ascending and descending order respectively.
We will get more grip on the topic, by considering an example.
Example 1: Using ORDER BY to Sort Table Data in Descending Order
We first need to create a table to sort its data in descending or ascending order. Let’s consider the following “exam_result” table, containing the columns for candidate names, candidate genders, marks of students in 2 exams, and the date on which the exam was conducted. I have already created it in my database. The table looks like this:
Now if we want to sort the data according to the marks of exam1, we will write the following query:
SELECT * FROM exam_result
ORDER BY exam1_marks DESC;
Executing this query will arrange the table’s data in DESC order. The results can be seen in the following output given by the query:
We can observe that the data of column, “exam1_marks” has been arranged in descending order. In a similar way, we can arrange the data in ascending order. We can also implement sorting on multiple columns.
Example 2: Sorting Multiple Columns Using the Order By Clause
We can apply multiple sorting orders to different columns of the same table. Let’s understand this by applying this concept to the above-considered table.
Let’s arrange the “exam1_marks” in descending order and “exam2_marks” in ascending order. The query can be written as:
SELECT * FROM exam_result
ORDER BY exam1_marks DESC , exam2_marks ASC;
The query will work in a way that the primary sorting will be done on the “exam1_marks” column i.e. it will sort the data in descending order and according to that the sorting will take place in the “exam2_marks” column. The query returns the following output:
This can be clearly seen that the “exam1_marks” column is primarily sorted in descending order while the “exam2_marks” column is sorted in ascending order according to the “exam1_marks”. What this simply means is that the DESC sorting is first applied to the “exam1_marks” column and then keeping in view the structure, the query applies ASC sorting to the “exam2_marks” column. This is how multiple sorting can be implemented to multiple Postgres columns.
Example 3: Sorting Data By Default ORDER BY Clause PostgreSQL
We can also sort the data in a default manner. This happens when we do not write the sorting order in the query. Let’s sort the “exam_mark” table by cand_name in a default order. To implement this, the following query can be executed:
SELECT * FROM exam_result
ORDER BY cand_name;
The default sorting of the candidate names means that the names will be sorted in alphabetical order. On execution, the query gives the following table.
The names of all the candidates in the “cand_name” column are sorted in alphabetical order by default.
Example 4: Limiting The Sorted Rows Using The ORDER BY
We can also get some limited data from a Postgres table after sorting it. For this, we will need to first order the data using the ORDER BY clause and then apply the LIMIT clause. The LIMIT clause returns the limited number of rows from the table. The number of rows has to be specified in the query after the LIMIT keyword.
Use Case:
Let’s suppose we want to get the data of the candidates who secured/got the top 3 positions in exam 1. For this, we will first sort the “exam1_marks” in descending order and then limit the data to 3 rows. The query for this case is:
SELECT * FROM exam_result
ORDER BY exam1_marks DESC
LIMIT 3;
This will give the data rows for the top 3 positioned students on the basis of exam1_marks like this:
The query retrieves the data of the top 3 candidates for exam 1. We can limit the data according to our wishes.
Example 5: Sort Data by Date Using the ORDER BY Clause
We can sort the data with respect to the date. This thing has various use cases. It can be used when we want to analyze everyday data. In the above-considered example, the randomly registered entries can be sorted on the basis of “date_of_test”. The query, for this purpose, will be:
SELECT * FROM exam_result
ORDER BY date_of_test ASC;
This query will sort the data of the “exam_result” table with respect to the date_of_test in ascending order i.e. the exam given first will be listed on the top and so on. The result of the query is:
In this way, we can sort the data according to dates.
Example 6: Using Null With Order By Clause
The database table data usually contains the NULL values, maybe due to some discrepancies and many other factors. We can also sort the data in accordance with the NULL values in the data table. For that NULLS FIRST and NULLS LAST are used. The NULLS FIRST statement sorts all the null values in the table column at the beginning while the NULLS LAST will sort the null values to the end of the table column.
Let’s add some null values to the above-considered table by using the below query:
INSERT INTO exam_result( cand_name, cand_gender, exam1_marks, exam2_marks, date_of_test)
VALUES('Sarah','Female', NULL ,63, '2023-8-21'),
('joe','Male',NULL,56, '2023-4-25'),
('Ben','Male',97, NULL,'2023-2-3');
This query will insert the above values in the table. The table will now look like this:
Now if we want to get the NULL values for the “exam1_column” at the end of the table, we will write the following query:
SELECT * FROM exam_result
ORDER BY exam1_marks NULLS LAST;
The above query will sort the table in a way that all the values containing NULL in the “exam1_column” will be placed at the end of the table like this:
We can also bring them to the beginning by using the NULLS FIRST statement like this:
SELECT * FROM exam_result
ORDER BY exam1_marks NULLS FIRST;
This will return the table having the NULL values for the “exam1_table” at the beginning.
This is how we can sort NULL values in any table.
Example 7: Using GROUP BY With Order By Clause.
The GROUP BY clause can also be paired with the ORDER BY clause. The GROUP BY clause groups the table data, on some specified basis. We can implement this use case when we want to get the average marks from both genders that is male and female.
To do this particular thing, we’ll group the data by cand_gender. Then apply the AVG() function which is an aggregate function to compute the average of the specified parameter. The query for this purpose can be written as:
SELECT cand_gender, AVG(exam1_marks) AS average_marks FROM exam_result
GROUP BY cand_gender
ORDER BY AVG(exam1_marks);
In this query, we have grouped the table data based on the gender of the candidates and we have applied the AVG() function to get the average. The output of the above query can be written as:
The GROUP BY and the ORDER BY clauses are used together to perform a wide variety of useful functions.
Example 8: Randomly Sorting Data Using The ORDER BY Clause
The data can be randomly sorted as well. This functionality is provided by the RANDOM() function when used with the ORDER BY clause. The query for the implementation of this function can be written as:
SELECT * FROM exam_result
ORDER BY RANDOM();
The query will sort the table data randomly like this:
We can observe that the data is sorted without any underlying set criteria.
Conclusion
The ORDER BY clause sorts the data in a table. The sorting order can be specified as descending or ascending based on the user’s need. There are different use cases of the ORDER BY clause which we have discussed in the above section. The ORDER BY clause can be paired up with various other commands to provide the custom/specific sorting order.
