Coming into this third blog post on Aggregate Functions, I will visit 2 powerful functions: MIN() and MAX(). They provide a broad range of functionality you can leverage. What sets them apart, is their ability to operate on multiple data types.
Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.
Photo by Volkan Olmez on Unsplash
OS and DB used:
- Xubuntu Linux 16.04.3 LTS (Xenial Xerus)
- MySQL 5.7.21
Be sure a visit both blog posts in the series so far:
- Aggregate Functions Part 1: COUNT() – With examples in MySQL
- Aggregate Functions Part 2: SUM() and AVG() in MySQL with examples.
Here is the description of the mock HR employees table we have been targeting for our queries:
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| EMPLOYEE_ID | decimal(6,0) | NO | PRI | 0 | |
| FIRST_NAME | varchar(20) | YES | | NULL | |
| LAST_NAME | varchar(25) | NO | MUL | NULL | |
| EMAIL | varchar(25) | NO | UNI | NULL | |
| PHONE_NUMBER | varchar(20) | YES | | NULL | |
| HIRE_DATE | date | NO | | NULL | |
| JOB_ID | varchar(10) | NO | MUL | NULL | |
| SALARY | decimal(8,2) | YES | | NULL | |
| COMMISSION_PCT | decimal(2,2) | YES | | NULL | |
| MANAGER_ID | decimal(6,0) | YES | MUL | NULL | |
| DEPARTMENT_ID | decimal(4,0) | YES | MUL | NULL | |
+----------------+--------------+------+-----+---------+-------+
11 rows in set (0.00 sec)
MIN and MAX
MIN() and MAX() return the smallest or largest value out of a range of values, respectively.
But, that holds different implications dependent upon the data type operated on.
MIN() and MAX() can both accept either numeric, string, or date data type arguments.
Numeric Values
Let’s see how both functions operate on numeric values.
Suppose we have this range of 5 SALARY column values for the 'FI_ACCOUNT' role:
-> FROM employees
-> WHERE JOB_ID = 'FI_ACCOUNT';
+-------------+---------+------------+
| EMPLOYEE_ID | SALARY | JOB_ID |
+-------------+---------+------------+
| 109 | 9000.00 | FI_ACCOUNT |
| 110 | 8200.00 | FI_ACCOUNT |
| 111 | 7700.00 | FI_ACCOUNT |
| 112 | 7800.00 | FI_ACCOUNT |
| 113 | 6900.00 | FI_ACCOUNT |
+-------------+---------+------------+
5 rows in set (0.00 sec)
I’ll apply MIN() and MAX() to this host of SALARY column values:
-> FROM employees
-> WHERE JOB_ID = 'FI_ACCOUNT';
+-------------+
| MIN(SALARY) |
+-------------+
| 6900.00 |
+-------------+
1 row in set (0.00 sec)
mysql> SELECT MAX(SALARY)
-> FROM employees
-> WHERE JOB_ID = 'FI_ACCOUNT';
+-------------+
| MAX(SALARY) |
+-------------+
| 9000.00 |
+-------------+
1 row in set (0.00 sec)
* Note: I removed the EMPLOYEE_ID and JOB_ID from the SELECT clause to prohibit needing a GROUP BY clause for demonstration purposes without error.
In these example queries, MIN() returned 6900.00 -the smallest value out of the list- and MAX() the largest, 9000.00.
As you can see, numeric value operations with MIN() and MAX() are relatively straight-forward.
Date Values
Date values are handled a little differently than numbers. The most current, newest date is considered the MAX() value, while MIN() returns the oldest date.
Some sample queries will help put this into perspective:
-> FROM employees;
+----------------+
| MAX(HIRE_DATE) |
+----------------+
| 1987-10-01 |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT MIN(HIRE_DATE)
-> FROM employees;
+----------------+
| MIN(HIRE_DATE) |
+----------------+
| 1987-06-17 |
+----------------+
1 row in set (0.00 sec)
We’ll view a query with both functions side-by-side for a nice visual of the difference:
-> FROM employees;
+----------------+----------------+
| MAX(HIRE_DATE) | MIN(HIRE_DATE) |
+----------------+----------------+
| 1987-10-01 | 1987-06-17 |
+----------------+----------------+
1 row in set (0.01 sec)
As shown above, 1987-10-01 is a more recent date than 1987-06-17 in respect to both dates returned.
String Values
MIN() and MAX() can operate on string values as well. Let’s look at the FIRST_NAME column and test out both functions on that range of values for the 'FI_ACCOUNT' role:
-> FROM employees
-> WHERE JOB_ID = 'FI_ACCOUNT';
+-------------+
| FIRST_NAME |
+-------------+
| Daniel |
| John |
| Ismael |
| Jose Manuel |
| Luis |
+-------------+
5 rows in set (0.00 sec)
-> FROM employees
-> WHERE JOB_ID = 'FI_ACCOUNT';
+-----------------+
| MIN(FIRST_NAME) |
+-----------------+
| Daniel |
+-----------------+
1 row in set (0.00 sec)
-> FROM employees
-> WHERE JOB_ID = 'FI_ACCOUNT';
+-----------------+
| MAX(FIRST_NAME) |
+-----------------+
| Luis |
+-----------------+
1 row in set (0.01 sec)
From the range of names returned by the first query, the following queries show that: MIN() and MAX() consider alphabetical ordering in this context.
* FYI:
MIN() and MAX()‘s functionality can potentially hinge on the character set and collation used within MySQL.
For instance, to know the system-wide setting for my local install, we can see with this SHOW VARIABLES statement:
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| character_set_system | utf8 |
+----------------------+-------+
1 row in set (0.01 sec)
The character set at the ‘system-level’ is utf8.
And for the mock employees table I am using throughout these blog posts,
*************************** 1. row ***************************
Table: employees
Create Table: CREATE TABLE `employees` (
..........
..........)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
latin1 is enabled as the character set.
Admittedly, this is a ‘gray area’ for me in MySQL.
Yet, I continue to study the official documentation from Chapter 10 Character Sets, Collations, Unicode to better understand my knowledge base ‘gap’ in regards to collation.
I encourage you all to consult that resource as well, for any questions you may have regarding character sets and collation.
Conclusion
Wrapping up this series, I hope you have a better understanding of those aggregate functions covered. I’ve not covered all aggregate functions available in MySQL, mainly just those you’ll likely encounter.
They all provide a multitude of uses. I would love knowing how you utilized them in various contexts, so feel free to leave a comment below.
Visit the official MySQL 5.7 On-line Manual for more information.
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Have I mentioned how much I love a cup of coffee?!?!
To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.