Min in CockroachDB 1321
In this lesson, we will learn to use Min in our CockroachDB queries. We will come at the lesson from different perspectives, so as to impart as deep a level of understanding as possible. In this lesson we will study:
- What? From a novice level coder perspective, what is the MIN aggregate function in Cockroach and what does it do for us?
- Why? In what situations do we need the Min aggregate function in our CockroachDB SQL?
- How? How do we use the MIN function in our Cockroach SQL?
- Novice-level knowledge of writing SQL for CockroachDB (or MS SQL Server, Postgres, MySQL, etc.) using the free Dbeaver tool (new support for Cockroach) or via scripting or coding server-side languages like VB.Net, Python, PHP, Java, C#, ASP.Net, Ruby, Node.js, etc. that provide Cockroach database connection, as well as a method for sending it SQL queries, to get data or make changes to your database.
- Understanding of the use of basic SQL (SQL) statements, like SELECT, FROM, and GROUP BY.
- Experience of what CockroachDB strings and functions are.
MIN aggregate function
The CockroachDB MIN function returns the lowest (minimum) of a set of numbers.
Min function syntax
intLowestValue := MIN(group_of_numbers);
Analysis: In the syntax above, you can see intLowestValue gets filled with the smallest value within a group of numeric values that are the one parameter of the MIN function.
When and why do we use the MIN aggregate function? When you have a group of numbers and you want to know which of the values in that group has the lowest value.
We’ll make the first example simple and easy. Our initial dataset is a table called tblProgLanguages.
At our company, as you can see above, our table called tblProgLanguages has three columns, including: – txtName is the name of the coding language. – intDevRapid is a numeric (integer) evaluation of how rapidly a coder at our fictional firm can develop applications using the language.
Our project goal is to find out which language has the lowest rating in terms of rapid development (intDevRapid). We’ll now write the SQL script to use the MIN function to find the language that has the lowest value in the intDevRapid column in our CockroachDB table called tblProgLanguages:
Please notice that for the SQL example above, this is where we bring our understanding of SQL GROUPing into the equation. Think of it this way: If you want to find the MINimum of more than one number, you need to consider a group of numbers.
How does the CockroachDB SQL statement above work? We’ll examine it step by step:
- (1) SELECT: We are telling CockroachDB’s query engine that we want the lowest of all values (because of having no HAVING or WHERE clause) in the intDevRapid column.
- (2) FROM: Specifying the tblProgLanguages Cockroach table to get the data from.
- (3) GROUP BY: Tells Cockroach that we want to group the data. The MIN aggregate function requires grouping of our data.
The above query returns: | idTech | txtName | intDevRapid | |:——:|————|————:| | 1 | Java | 4 |
We can now report back to the CEO that Java is the slowest in terms of rapid development in our firm. Can you guess what change to your query the CTO will next ask for? Yep, to use the Max aggregate function to find out which technology is rated fastest for rapid development. But we will save that for another document.
Min example in CockroachDB
The next mini project: We had employees in our firm grade the technologies used and want to know the lowest 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 “tblTechnologies”:
We will start with a plan. The following SQL will accomplish a few things, 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 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 txtTechnology 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 txtEmployee column.
- (4) Get a MIN of SUM of the values of those 5 rows using the intGrade column.
- (5) Using GROUP BY to group the appropriate columns.
, SUM(intGrade) AS intSum_of_grades
, MIN(intSum_of_grades) AS intLowestValue_grade
txtCatTech = 'Lang'
Analysis of the above query:
- (1) SELECT: We are filtering the Cockroach table to: (a) show the txtTechnology column; (b) calculate the sum of grades with Sum(intGrade) for each given technology; (c) Show the minimum of those sums with Min(intSum_of_grades); and (d) name the result as intLowestValue_grade.
- (2) FROM: Specifying the tblProgLanguages Cockroach table as the source of our data.
- (3) GROUP BY: Notifying CockroachDB that we are grouping. The MIN aggregate function requires for us to group the data that Cockroach is summing up and finding the lowest value of.
- (4) HAVING: This is where we tell CockroachDB to only consider the rows that have “Lang” in the txtCatTech column.
The query above returns:
Here in this lesson document, we learned when and how it is best to use the MIN function with Cockroach database queries. We showed this through the use of two examples, one easy and one complex. We slowly scaled difficulty up so as to make the learning process as easy and logical as possible because SQL can sometimes times be difficult to wrap your head around, especially when using the HAVING and GROUP BY statements to work with subsets of data. Code samples are included.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started