Yogesh Chauhan's Blog

Order By and Group By in Postgres

in Postgres on April 15, 2020

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


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 How to uninstall Cocoapods from the Mac OS? #6 PHP Login System using PDO Part 1: Create User Registration Page

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
How to compile and watch Sass using Gulp in WordPress?SCSSHow to create a CSS full page background image?CSSaddcslashes() and addslashes() String Functions in PHPPHPHow to add Laravel to WordPress using Sage theme (and install Tailwind CSS)?PHPOrder By and Group By in PostgresPostgresHow does Binding work in JavaScript?JavaScript