Yogesh Chauhan's Blog

Order By and Group By in Postgres

in Postgres on April 15, 2020


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;


"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;

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;

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"


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);


SELECT country FROM customers GROUP BY country;



With Aggregate Function

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


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));


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. 


Most Read

#1 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #2 How to add Read More Read Less Button using JavaScript? #3 How to check if radio button is checked or not using JavaScript? #4 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to uninstall Cocoapods from the Mac OS?

Recently Posted

#Apr 8 JSON.stringify() in JavaScript #Apr 7 Middleware in NextJS #Jan 17 4 advanced ways to search Colleague #Jan 16 Colleague UI Basics: The Search Area #Jan 16 Colleague UI Basics: The Context Area #Jan 16 Colleague UI Basics: Accessing the user interface
You might also like these
Reading Multiple Inputs in SwiftSwiftSQL GROUP BY StatementSQL/MySQLHow to get user’s Browser and Operating Systems information using PHP?PHPHow does while loop work in SCSS?SCSSRIGHT JOIN in PostgresPostgresAlways add associated labels to your Form elementsUI/UX