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 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
Containers 101: What are containers?MiscellaneousWhat does JSX do in React?ReactHow to use PHPMailer to send an Email via Gmail SMTP Server?PHPFive common features of Angular template syntax (with examples)AngularWindow innerHeight and innerWidth properties in JavaScriptJavaScriptPostgreSQL BETWEENPostgres