Order By and Group By in Postgres

ORDER BY

To sort the result set, we can use the ORDER BY clause in the SELECT statement in Postgres just like SQL.


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

The official documentation provides a very complex syntax:


SELECT select_list
    FROM table_expression
    ORDER BY sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }]
             [, sort_expression2 [ASC | DESC] [NULLS { FIRST | LAST }] ...]

We will go though that in future blog post.

For the following examples, I am using this database from my public repo

Let's take a look at the examples first:


SELECT address, city FROM customers ORDER BY city;

//Output

"Walserweg 21"	"Aachen"
"2817 Milton Dr."	"Albuquerque"
"2743 Bering St."	"Anchorage"
"Rambla de Cataluña, 23"	"Barcelona"
....

When more than one expression is specified, the later values are used to sort rows that are equal according to the earlier values.

Each expression can be followed by an optional ASC or DESC keyword to set the sort direction to ascending or descending, just like SQL.

ASC order is the default, just like SQL.

Very basic explanation about ASC and DSC: Ascending order puts smaller values first, where "smaller" is defined in terms of the < operator. Similarly, descending order is determined with the > operator.

Let's take a look at another example:


SELECT address, city FROM customers ORDER BY city ASC, address DESC;

//Output
address.    city
"Walserweg 21"	"Aachen"
"2817 Milton Dr."	"Albuquerque"
"2743 Bering St."	"Anchorage"
"Rambla de Cataluña, 23"	"Barcelona"
"Carrera 52 con Ave. Bolívar #65-98 Llano Largo"	"Barquisimeto"
"Via Ludovico il Moro 22"	"Bergamo"
"Obere Str. 57"	"Berlin"
"Hauptstr. 29"	"Bern"
"187 Suffolk Ln."	"Boise"
"Maubelstr. 90"	"Brandenburg"
"Rue Joseph-Bens 532"	"Bruxelles"
"Åkergatan 24"	"Bräcke"

Sort rows by expression

We can sort the rows or a particular column by the length of the string or similar expression. For example,


SELECT address, city FROM customers ORDER BY LENGTH(city) ASC, LENGTH(address) DESC;

///Output
address          city
"2, rue du Commerce"	"Lyon"
"Mehrheimerstr. 369"	"Köln"
"8 Johnstown Road"	"Cork"
"Kirchgasse 6"	"Graz"
"Hauptstr. 29"	"Bern"
"Torikatu 38"	"Oulu"
"City Center Plaza 516 Main St."	"Elgin"
"Garden House Crowther Way"	"Cowes"
"184, chaussée de Tournai"	"Lille"
"265, boulevard Charonne"	"Paris"
"55 Grizzly Peak Rd."	"Butte"
"59 rue de l'Abbaye"	"Reims"

GROUP BY

As the name suggests, it just divide rows into groups, just like SQL.


SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

Example:


SELECT country FROM customers GROUP BY country;

//Output

country
"Argentina"
"Spain"
"Switzerland"
"Italy"
"Venezuela"
"Belgium"
"Norway"
...

With Aggregate Function


SELECT COUNT(city), country FROM customers GROUP BY country;

//Output

count country
3	"Argentina"
5	"Spain"
2	"Switzerland"
3	"Italy"
4	"Venezuela"
......

A bit Complex Example with Multiple Aggregate Functions


SELECT COUNT(city), MAX(LENGTH(region)), country FROM customers 
GROUP BY country ORDER BY MAX(LENGTH(region));

//Output

count.  max.   country
9	2	"Brazil"
13	2	"USA"
3	6	"Canada"
1	8	"Ireland"
4	13	"Venezuela"
7	13	"UK"

In the query above, we are just counting the number of cities each country have and then sorting them by the length of the region's name in default (ASC) order.

Sources

« Previous Next »