Max in CockroachDB
In this document, we will learn to use aggregate Max in CockroachDB SQL scripts from multiple perspectives, to gain a deeper understanding of the Max aggregate function. In this tutorial document we will discuss and explore:
- What? From a novice perspective, what the MAX aggregate function does?
- Why? In what situations should we use the CockroachDB Max function?
- How? How do we use the aggregate Max function in CockroachDB SQL?
- More We’ll also make use of the GROUP BY and HAVING clauses.
- Beginner-level knowledge of building SQL for CockroachDB (or other relational databases such as Postgres, MySQL, MS SQL Server, etc.) using the Dbeaver admin GUI or via code or scripting server-side languages like Python, PHP, Java, C#, ASP.Net, VB.Net, Node.js, etc. that provide connection to CockroachDB, as well as a method for sending SQL queries to CockroachDB, to insert, update, or retrieve data from your relational database.
- Understanding of the use of some basic SQL statements, like SELECT, FROM, and GROUP BY.
What is the MAX aggregate function?
The Cockroach MAX function returns the largest (maximum) value from a set of numeric values. Use the Max aggregate function in Cockroach any time you have a set of numbers where you want to know which of that set of values is the largest number in the group.
Max function syntax
intLargestOfGroup := MAX(group_of_numeric_values);
Max function example
We’ll start with an easy-to-understand example. Our initial dataset is a table called tblProgLanguages.
At our imaginary software development company, the above table called tblProgLanguages has three columns, including:
- idTech is our unique programming language identifier.
- txtName is the name of the coding language.
- intDevRapid is an integer evaluation of how rapidly a company programmer can develop using the language.
Our test project goal is to find out which programming language has the best (highest) rating in terms of rapid development (intDevRapid). We will write the first SQL script to use the MAX function to retrieve the txtName that has the highest value in the intDevRapid column in our CockroachDB database table called tblProgLanguages:
Note regarding the CockroachDB SQL example above: This is where we use our understanding of SQL GROUPs. You can think of it this way: If you want to find the maximum of more than one value, you need to consider a group of numeric values.
How does the CockroachDB SQL statement above work? We will take it step by step:
- (a) SELECT: We are telling CockroachDB’s SQL engine that we want the maximum value from all the values (because no WHERE or HAVING clause) in the intDevRapid column of tblProgLanguages.
- (b) FROM: Specifying the tblProgLanguages table to get our data from.
- (c) GROUP BY: Tells Cockroach that we want to group the data. The MAX() function, being an aggregate function, requires grouping of the data.
The above query returns:
We now know that Python is the fastest in terms of rapid development, company-wide.
Max SQL function example
The next mini project: We had employees grade the coding and database technologies used at the company and want to know the highest rated technology. Here we will explore another relatively simple use of CockroachDB MAX while introducing the “HAVING” clause. Here’s our dataset from the table named tblLanguagesAndDBs:
|6||Julie||MS SQL Server||Lang||39|
Let’s make a plan for 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 txtCatTech column value is equal to “Lang”. This is where the HAVING clause comes in handy. It will yield 4 rows. Can you see why? Hint: Look in the table above for the “Lang” value in the txtCatTech column. Notice there are four rows that match this criteria?
- (2) Return the value in the txtTechnology field for each of those 4 rows because we want to know which technologies the grades go with.
- (3) We don’t need to know who made the assessment, so no need to mention the txtEmployee column in our SQL.
- (4) Get a MAX of SUM of the values of those four rows using the intGrade column.
- (5) Use the GROUP BY clause to group the appropriate columns.
, SUM(intGrade) AS intSum_of_grades
, MAX(intSum_of_grades) AS intHighestGrade
HAVING txtCatTech = 'Lang'
Analysis of the SQL above:
- (1) SELECT: We are filtering our Cockroach tblLanguagesAndDBs table to: (a) Show the txtTechnology column, (b) calculate the sum of grades (Sum(intGrade)) for each programming language or database system, and (c) Show the maximum of those sums (Max(intSum_of_grades)) and name the result as intHighestGrade.
- (2) FROM: Specifies the tblLanguagesAndDBs table as the source for our CockroachDB data.
- (3) GROUP BY: Notify CockroachDB that we are grouping. The MAX function requires for us to group the column(s) that Cockroach is internally summing up and finding the highest of.
- (4) HAVING: This is where we instruct CockroachDB to filter for the rows that have “Lang” in the txtCatTech column because we only want to look at programming languages, not database systems.
The query above returns:
In this tutorial document we learned when and how it is best to use the MAX function in Cockroach to find the highest valued numeric value in a group of numbers. We used both novice-level and more difficult examples, scaling up the difficulty as we went, so as to make the learning process as easy as possible because SQL can at times be difficult to understand, especially when you use the HAVING and GROUP BY clauses to work with subsets of records. We invite you to look for our other documents that utilize the GROUP BY CockroachDB clause.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started