Use of Max function in Postgres SQL
Introduction
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.
Prerequisites
- Beginner-level understanding of writing SQL for PostgreSQL (or Oracle, MySQL, MS SQL Server, etc.) using the PG Admin tool or some other db administration tool, or by via code or script/coding languages like PHP, Python, Javascript, Java, C#, ASP.Net, VB.Net, Ruby, Node.js, etc.) that provide a database connection, as well as a method for sending the database SQL queries, to get data or make changes to your database.
- Understanding of the use of some basic SQL (PL/SQL) statements, like
SELECT
,FROM
, andGROUP BY
. - Knowledge of what a string and function are.
What is the MAX() function and how does it work?
The Postgres MAX()
function returns the highest (maximum) value from a set of numbers.
Syntax
1 | 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.
id_tech | t_name | i_rapid_dev |
---|---|---|
0 | Python | 7 |
1 | Java | 5 |
2 | PHP | 9 |
3 | Javascript | 8 |
At our software development company, the above table called coding_languages
has three fields, including:
id_tech
is our unique identifier.t_name
is the name of the coding language.i_rapid_dev
is 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:
1 2 3 4 5 6 7 8 | SELECT id_tech , t_name , MAX(i_rapid_dev) FROM coding_languages GROUP BY i_rapid_dev; |
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:
- (1)
SELECT
: We are telling PostgreSQL’s SQL engine that we want a maximum of all values (because no WHERE or HAVING clause) in thei_rapid_dev
column. - (2)
FROM
: Specifying thecoding_languages
table to retrieve our data from. - (3)
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”:
id_tech | t_name_user | t_name_tech | t_category_tech | i_grade |
---|---|---|---|---|
0 | Ted | Java | Lang | 4 |
1 | Bif | Mongo | NoSQL | 16 |
2 | Susan | MySQL | RDB | 8 |
3 | Tim | PostgreSQL | RDB | 16 |
4 | Gary | Python | Lang | 20 |
5 | Sammy | PHP | Lang | 12 |
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.
1 2 3 4 5 6 7 8 9 10 11 | SELECT t_name_tech , SUM(i_grade) AS i_sum_of_grades , MAX(i_sum_of_grades) AS i_highest_grade FROM coding_languages GROUP BY t_name_tech , i_grade; HAVING t_category_tech = 'Lang' |
Analysis of the SQL above:
- (1)
SELECT
: We are filtering our Postgres table to: (a) Show thet_name_tech
column, (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 asi_highest_grade
. - (2)
FROM
: Specifying thecoding_languages
table as the source of our data. - (3)
GROUP BY
: Notifying PostgreSQL that we are grouping. TheMAX
function requires for us to group the data that Postgres is internally summing up and finding the highest of. - (4)
HAVING
: This is where we instruct PostgreSQL to filter in the rows that have “Lang” in thet_category_tech
field.
The query above returns:
t_name_tech | t_category_tech | i_grade |
---|---|---|
PHP | Lang | 12 |
Notes
- 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 BY
Postgres clause.
Conclusion
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