Use of Max function in Postgres SQL
In this article, we’ll learn to use the aggregate Max function in our Postgres SQL commands from multiple perspectives, to gain a deeper level of understanding. Here we will discuss:
- What? From a beginner perspective, what the MAX function does?
- Why? In what situations should we use the Max() function?
- How? How do we use this function in our SQL?
- More We’ll also make some use of the GROUP BY construct.
- Understanding of the use of some basic SQL (PL/SQL) statements, like
- Knowledge of what a string and function are.
What is the MAX() function and how does it work?
MAX() function returns the highest (maximum) value from a set of numbers.
i_highest_of_set := MAX(set_of_numbers);
Why and when do we use the MAX() function?
Any time you have a set of numbers where you want to know which of that set is the highest in set.
Let’s start with an easy example
We’ll begin with an easy-to-understand example. Our initial dataset is a table called coding_languages.
At our software development company, the above table called
coding_languages has three fields, including:
id_techis our unique identifier.
t_nameis the name of the coding language.
i_rapid_devis an integer evaluation of how rapidly an average coder can develop using the language.
The goal is to find out which language has the highest rating in terms of rapid development (
i_rapid_dev). Let’s write our SQL script to use the
MAX function to retrieve the
t_name that has the highest value in the
i_rapid_dev column in our PostgreSQL database recordset:
Note for the SQL example above: This is where we use our knowledge of SQL GROUPs. You can think of it this way: If you want to find the maximum of more than one number, you need to consider a set of numbers.
How does the SQL statement above work? We’ll take it step by step:
SELECT: We are telling PostgreSQL’s SQL engine that we want a maximum of all values (because no WHERE or HAVING clause) in the
FROM: Specifying the
coding_languagestable to retrieve our data from.
GROUP BY: Telling Postgres that we want to group the data. The MAX() function, being an aggregate function, requires grouping of our data.
The above query returns: | id_tech | t_name | i_rapid_dev | |:——-:|——–|————:| | 2 | PHP | 9 |
We now know that PHP is the fastest in terms of rapid development.
Another example of using the “Max” SQL function in PostgreSQL
The mini project: We had employees grade the technologies used at our company and want to know the highest graded tech. Here we will explore another relatively simple use of
MAX() while introducing the “HAVING” key word. Here’s our “raw” dataset with the table name of “technologies”:
Let’s plan how to proceed. We want to build a query that will do more than one thing, including:
- (1) Only consider the rows in our data set where the _t_categorytech field (column) value is equal to “Lang”. This is where the HAVING clause comes in handy. It will yield 3 rows. Can you see why? Hint: Look in the table above for the “Lang” value. Notice there are three? Which column are they in?
- (2) Return the value in the _t_nametech field for those 3 rows because we want to know which technologies the grades go with.
- (3) We don’t care who made the assessment, so no mention of the _t_nameuser column.
- (4) Get a MAX of SUM of the values of those 3 rows using the _igrade column.
- (5) Use GROUP BY to group the appropriate columns.
, SUM(i_grade) AS i_sum_of_grades
, MAX(i_sum_of_grades) AS i_highest_grade
t_category_tech = 'Lang'
Analysis of the SQL above:
SELECT: We are filtering our Postgres table to: (a) Show the
t_name_techcolumn, (b) calculate the sum of grades (
Sum(i_grade)) for each given technology, and (c) Show the maximum of those sums (
Max(i_sum_of_grades)) and name the result as
FROM: Specifying the
coding_languagestable as the source of our data.
GROUP BY: Notifying PostgreSQL that we are grouping. The
MAXfunction requires for us to group the data that Postgres is internally summing up and finding the highest of.
HAVING: This is where we instruct PostgreSQL to filter in the rows that have “Lang” in the
The query above returns:
- Why did we name some of our variables and table columns with a prefix of “i” or “t“? We use “i” to mean integer and “t” to mean text or string.
- We invite you to look for our other articles that utilize the powerful
GROUP BYPostgres clause.
Here we learned when and how it is best to use the
MAX() function in PostgreSQL to find the highest valued number in a set of numbers. We used both beginner-level and more difficult examples, scaling up difficulty, so as to make the learning process as easy as possible because SQL can at times be difficult to wrap your head around, especially when you use the
GROUP BY and
HAVING clauses to work with subsets of data.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started