In database management systems, organizing the tables’ data is a crucial task. In databases like PostgreSQL, MySQL, ORACLE, etc., the ORDER BY clause is used to specify the table’s data in a particular order. In Postgres, the need of sorting the table’s data by count arises while working with the aggregated data. In such cases, the users must be aware of which column should be used when sorting the table's data.

This write-up will present a detailed guide on how to order the table’s data by count in Postgres.

How to Order Table’s Records by Count in PostgreSQL?

In Postgres, the COUNT() function calculates the total number of records in a table while the ORDER BY clause sorts the data in a certain order. Utilizing the ORDER BY clause with the COUNT() function will retrieve the table’s records by count/quantity. Here is the simple yet very effective syntax:

SELECT col_list, COUNT(col_name)
FROM tab_name
GROUP BY col_name
ORDER BY COUNT(col_name) ASC | DESC;

- The COUNT() is an aggregate function, so it must be utilized with the GROUP BY clause.
- The “ORDER BY COUNT()” will sort the table's data based on the count.

Example 1: How to Sort By Count in Postgres?

In the following example code, we will utilize an already created sample table named “bike_info”:

SELECT * FROM bikes_info;

Let’s execute the following statement to sort the table’s data by count:

SELECT bike_price, COUNT(bike_model)
FROM bikes_info
GROUP BY bike_price
ORDER BY COUNT(bike_model);

- The above-stated command will group the bikes by their price.
- It will count the number of bikes for each price group.
- We didn’t specify the “ASCE” or “DSCE” option in the “ORDER BY COUNT()” command, so the result set will be sorted in ascending order, by default.
- Consequently, the bikes that have the least count will occur at the top.

The result set shows that the bikes’ data is sorted by count (in ascending order).

Example 2: Sort By Count in Descending Order

Use the “DESC” option with the “ORDER BY COUNT()” to sort the table’s records in descending order:

SELECT bike_price, COUNT(bike_model)
FROM bikes_info
GROUP BY bike_price
ORDER BY COUNT(bike_model) DESC;

The result set shows that the bikes’ data is sorted by count (in descending order).

Conclusion

In Postgres, the COUNT() function calculates the total number of records in a table while the ORDER BY clause sorts the data in a certain order. Utilizing the ORDER BY clause with the COUNT() function will retrieve the table’s records by count/quantity. The “ASCE” and “DSCE” options determine the sorting order of the result set. Omitting the “ASCE” or “DSCE” option will by default sort the table’s data in ascending order. This article has demonstrated a practical guide on how to order by count in Postgres.