/ ID: 793 Title: Use of Max function in Postgres SQL 793 Meta Description: This tutorial shows why and how we use the Max function in Postgres SQL. Code samples included. Meta Keywords: Max Function Postgres SQL Author: orkb Template: Unstructured Tutorial Categories: PostgreSQL Tags: Max, Function, PostgreSQL, SQL, Group by Status: Published /

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, and GROUP 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 SQL 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:

SQL 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 the i_rapid_dev column.
• (2) FROM: Specifying the coding_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_category_tech 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_name_tech 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_name_user column.
• (4) Get a MAX of SUM of the values of those 3 rows using the i_grade column.
• (5) Use GROUP BY to group the appropriate columns.

SQL 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 the t_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 as i_highest_grade.
• (2) FROM: Specifying the coding_languages table as the source of our data.
• (3) GROUP BY: Notifying PostgreSQL that we are grouping. The MAX 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 the t_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.