YogeshChauhan.com

Order By and Group By in Postgres

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

dreamhost

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 uninstall Cocoapods from the Mac OS? #4 How to Use SQL MAX() Function with Dates? #5 How to add Read More Read Less Button using JavaScript? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

Apr 7 Solution for “Yarn build: Failed because of a stylelint error” Apr 7 Make sure your links have descriptive text Apr 7 How to add Laravel to WordPress using Sage theme (and install Tailwind CSS)? Apr 2 How to create a sidebar using pure CSS? Apr 1 How to switch dark and light themes using pure CSS? Apr 1 How to calculate elapsed time in JavaScript?

You might also like these

LEFT JOIN in PostgresPostgresIf statement shorthand examples in JavaScriptJavaScriptJavaScript Data Types and Data Structures Things to RememberJavaScriptHow to read Standard Input in Swift?SwiftLearn to make a responsive gallery using CSS Grid and without media queriesCSSHow to get the height and width of an element using JavaScript?JavaScript