Use of Min function in Postgres SQL
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?
- Understanding of the use of the most basic SQL (PL/SQL) statements, like
- Knowledge of what a string is and what a function is.
What is the MIN() function and how does it work?
MIN function returns the lowest (minimum) of a set of numbers.
i_lowest := MIN(set_of_numbers);
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_nameis the name of the coding language.
i_rapid_devis 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:
SELECT: We are telling PostgreSQL’s SQL engine that we want a minimum 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 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.
, SUM(i_grade) AS i_sum_of_grades
, MIN(i_sum_of_grades) AS i_lowest_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 minimum of those sums (
Min(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
MINfunction requires for us to group the data that Postgres is internally summing up and finding the lowest of.
HAVING: This is where we instruct PostgreSQL to only consider 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“? 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 BYPostgres clause.
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started