Use of Min function in Postgres SQL

Introduction

In this tutorial, we will learn to use the aggregate Min function in our Postgres SQL commands from more than one perspective, so as to gain as deep a level of understanding as possible. Here we will discuss:

• What? From a beginner level coder perspective, what is the MIN function and what does it do?
• Why? In what situations do we need the Min() function?
• How? How do we use this function in our SQL?

Prerequisites

• Beginner-level understanding of writing SQL for PostgreSQL (or MS SQL Server, Oracle, MySQL, etc.) using the free PG Admin tool or some other relational db admin tool, or by via code or script (compiled or not, object-oriented or not) languages like Python, PHP, Java, C#, ASP.Net, Javascript, VB.Net, Ruby, Node, etc.) that provides a database connection, as well as a method for sending it PL/SQL queries, to get data or make changes to your database.
• Understanding of the use of the most basic SQL (PL/SQL) statements, like SELECT, FROM, and GROUP BY.
• Knowledge of what a string is and what a function is.

What is the MIN() function and how does it work?

The PostgreSQL MIN function returns the lowest (minimum) of a set of numbers.

Syntax

Why and when would we use the MIN() function?

Any time you have a set of numbers where you want to know which of that set is the lowest in value.

A simple example first

Let’s begin with an easy example. Our initial dataset is a table called coding_languages.

At our company, as you can see above, our table called coding_languages has three fields, including:

• 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.

Our mini project is to find out which language has the lowest rating in terms of rapid development (i_rapid_dev). Let’s write the SQL code to use the MIN function to retrieve the language that has the lowest value in the i_rapid_dev column in our PostgreSQL database recordset:

note for the SQL example above. Here is where we bring our knowledge of SQL GROUPing into the mix. You can think of it this way: If you want to find the minimum 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 minimum 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 MIN() function, being an aggregate function, requires grouping of our data.

The above query returns: | id_tech | t_name | i_rapid_dev | |:——-:|————|————:| | 1 | Java | 5 |

We can now report back to the CTO that – as guessed – Java is the slowest in terms of rapid development, company-wide. Can you guess what change to your query the CTO will next ask for? Yep, using the Max() function to find out which technology is rated best for rapid development! But we’ll save that for another article.

A second example of using the “Min” SQL function in PostgreSQL

The mini project: We had employees grade the technologies used at our company and want to know the average grades. Here we will explore another relatively simple use of MIN() while introducing the “HAVING” key word. Here’s our “raw” dataset with the table name of “technologies”:

Let’s make a plan. The following query will do a few things, 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 5 rows. Can you see why? Hint: Look in the table above for the “Lang” value. Notice there are five? Which column are they in?
• (2) Return the value in the _t_nametech field for those 5 rows because we want to know which technologies the grades go with.
• (3) Explicitly NOT caring WHO made the assessment, so no mention of the _t_nameuser field (column).
• (4) Get a MIN of SUM of the values of those 5 rows using the _igrade column (field).
• (5) Using GROUP BY to group the appropriate columns.

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 minimum of those sums (Min(i_sum_of_grades)) and name the result as i_lowest_grade.
• (2) FROM: Specifying the coding_languages table as the source of our data.
• (3) GROUP BY: Notifying PostgreSQL that we are grouping. The MIN function requires for us to group the data that Postgres is internally summing up and finding the lowest of.
• (4) HAVING: This is where we instruct PostgreSQL to only consider the rows that have “Lang” in the t_category_tech field.

The query above returns:

Notes

• Why did we name some of our variables and table columns with a prefix of “i” or “t“? Short answer: 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 simple and useful GROUP BY Postgres clause.

Conclusion

Here we learned when and how it is best to use the MIN() function in PostgreSQL. We used both easy and complex examples, slowly scaling up difficulty, so as to ease the learning process 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.