MariaDB Greatest Function – Useful Guide

In this MariaDB tutorial, we will discuss how to use the MariaDB GREATEST function and also look at several examples related to it. There are lists of the topic that comes under discussion:

• MariaDB Greatest
• MariaDB Greatest Null
• MariaDB Greatest Date
• MariaDB Greatest-N-Per-Group
• MariaDB Greatest Join
• MariaDB Greatest Year

Also, check the latest MariaDB tutorial: MariaDB Create Database And User

MariaDB Greatest

In this section, we will learn how to use the MariaDB Greatest function in the query and which is explained with the help of an illustrated example.

In MariaDB, the GREATEST function will return the greatest value from the list of expressions of the query. Let’s see the syntax of the MariaDB Greatest function of the following query:

SYNTAX:

SELECT GREATEST(expression_1,expression_2,expression_n) FROM TABLE_NAME;

The syntax explanation:

• EXPRESSION_1, EXPRESSION_2, EXPRESSION_N: The expression that is evaluated by the GREATEST function.

NOTE:

• The MariaDB Greatest function returns the largest numeric value if a list of words in numeric value exists.
• The MariaDB Greatest function returns the largest string value if a list of words in the string value exists.

First, we create a table called MIT_STUDENTS by the following query:

CREATE AND INSERT:

CREATE TABLE MIT_STUDENTS (
	id INT,
	first_name VARCHAR(50),
	Maths INT,
	Physics INT,
	Computer_Science INT,
	Physical_Training INT
);
insert into MIT_STUDENTS (id, first_name, Maths, Physics, Computer_Science, Physical_Training) 
values (1, 'Jandy', 77, 22, 48, 66),
(2, 'Bianka', 59, 34, 29, 24),
(3, 'Carissa', 65, 18, 29, 98),
(4, 'Jacynth', 52, 56, 14, 91),
(5, 'Wendye', 5, 21, 48, 24),
(6, 'Mirabella', 28, 13, 36, 74),
(7, 'Colan', 31, 100, 38, 64),
(8, 'Cam', 20, 75, 30, 37),
(9, 'Ode', 21, 75, 36, 49),
(10, 'Heinrick', 13, 53, 31, 73);

SELECT * FROM MIT_STUDENTS;

Here, we have created a table called MIT_STUDENTS by using the CREATE TABLE statement. Then, we inserted some records into the MIT_STUDENTS table by using the INSERT INTO statement. If we want to see all records of the table, then we will use the SELECT statement.

Here is an illustrated example of the MariaDB greatest function by the following query:

EXAMPLE_1:

SELECT GREATEST(69.99,199.95,79.95);

The MariaDB GREATEST function will return the greatest value from all three parameters as 69.99, 199.95, and 79.95. The Greatest function will return 199.95 as the output result.

EXAMPLE_2:

Let’s have a look at the MariaDB Greatest function on the MIT_STUDENTS table by the following query:

SELECT GREATEST(MATHS,PHYSICS,COMPUTER_SCIENCE,PHYSICAL_TRAINING) AS GREASTEST_MARKS FROM MIT_STUDENTS;

The GREATEST function will be used in the preceding query to discover the greatest value among all the subjects on the first name column-wise. On the MATHS, PHYSICS, COMPUTER_SCIENCE, and PHYSICAL_TRAINING columns, we used the GREATEST function.

We used the AS keyword from the alias clause as the GREATEST_MARKS column to get the highest value from all subjects which were used from the MIT_STUDENTS table using the SELECT statement to make the greatest function concise.

Read: MariaDB Between

MariaDB Greatest Null

Here we will understand if we put one of the values of expression as NULL then what will happen to the result of the MariaDB GREATEST function in the query. And which will be explained with the help of an illustrated example.

EXAMPLE:

SELECT GREATEST(MATHS,PHYSICS,COMPUTER_SCIENCE,PHYSICAL_TRAINING) AS GREASTEST_MARKS FROM MIT_STUDENTS
WHERE id=11;

• In this query, we have to find the null value as the greatest value by using the GREATEST function on the MIT_STUDENTS table in the SELECT statement.
• And we have used the alias clause to shorter the GREATEST function name into the GREATEST_MARKS column in the output from the MIT_STUDENTS table.
• At the end of the query, we have used the WHERE condition for the ID column as 11. If the WHERE condition gets TRUE, then it will find the NULL value as the greatest value by using the GREATEST function of the MIT_STUDENTS table by using the SELECT statement.

Read: MariaDB Median

MariaDB Greatest Date

Here we will use the MariaDB GREATEST function on the DATE columns in the query and which is explained with the help of an illustrated example.

EXAMPLE:

SELECT GREATEST(JOINING_DATE,EXPIRING_DATE) AS GREATEST_DATE FROM FAZZRO_COMPANY 
LIMIT 5;

• In the preceding query, we used the GREATEST function on the JOINING_DATE and EXPIRING_DATE columns to find the greatest date value from the FAZZRO_COMPANY.
• To shorter the GREATEST function name, we have an alias clause for the output name as the GREATEST_DATE column by using the AS keyword.
• At the end of the query, we have used the LIMIT clause as LIMIT 5 to retrieve the first 5 records from the FAZZRO_COMPANY table by using the SELECT statement.

Read: MariaDB Not Equal Operator

MariaDB Greatest Join

In this section, we will learn how to use the GREATEST function with the JOIN clause in MariaDB. And we will explain it with the help of an example.

In MariaDB, JOIN is used to merge the rows from more than one table based on common columns in tables. In other words, The data is extracted from more than one table into a single table using the JOIN clause.

• The JOIN clause can be used when there are two or more two tables with common columns.

There are four types of JOIN in MariaDB:

• INNER JOIN: It is a simple JOIN that retrieves all the rows from more than one table where the JOIN condition is True.
• LEFT JOIN: It is a LEFT OUTER JOIN that retrieves all the rows from the left table based on whatever is specified in the ON condition and returns those rows from another table where the JOIN condition is True.
• RIGHT JOIN: It is a RIGHT OUTER JOIN that retrieves all the rows from the right table based on whatever is specified in the ON condition and returns those rows from another table where the JOIN condition is True.
• CROSS JOIN: It returns the result set where each row in a table is joined with each row in another table.

Here we will use the GREATEST function with the INNER JOIN clause of two tables by the following query:

EXAMPLE:

SELECT FAZZRO_COMPANY.FULL_NAME,GREATEST(FAZZRO_COMPANY.JOINING_DATE,FAZZRO_COMPANY.EXPIRING_DATE) AS GREATEST_DATE,
MIT_STUDENTS.FIRST_NAME,
GREATEST(MIT_STUDENTS.MATHS,MIT_STUDENTS.PHYSICS,MIT_STUDENTS.COMPUTER_SCIENCE,MIT_STUDENTS.PHYSICAL_TRAINING) AS GREATEST_MARKS
FROM FAZZRO_COMPANY
INNER JOIN MIT_STUDENTS
ON FAZZRO_COMPANY.ID=MIT_STUDENTS.ID;

In the output from the FAZZRO_COMPANY table, we utilized the GREATEST function to extract the best value of the JOINING_DATE and EXPIRING_DATE columns. And retrieve records from the FULL_NAME column.

Using the GREATEST function on the SELECT statement, we selected records from the FIRST_NAME column and calculated the highest marks for the MATHS, PHYSICS, COMPUTER_SCIENCE, and PHYSICAL_TRAINING columns for another table as the MIT_STUDENTS table.

We utilized the alias clause via AS keyword to shorten the title of the GREATEST function as an output shortcut and named the functions GREATEST_DATE and GREATEST_MARKS column.

Both tables were joined since the INNER JOIN clause condition on the FAZZRO_COMPANY and MIT_STUDENTS tables were TRUE.

And when the ON condition was met, it connected to the ID column of both the FAZZRO_COMPANY and the MIT_STUDENTS tables, resulting in the actual output set.

MariaDB Greatest-N-Per-Group

In this section, we will understand how to use the GREATEST function per group in the query and which is explained with the help of an illustrated example.

EXAMPLE:

SELECT * from MIT_STUDENTS
  WHERE (FIRST_NAME, MATHS) IN 
(
SELECT FIRST_NAME, GREATEST(MATHS,PHYSICS,computer_science) 
    FROM MIT_STUDENTS
    GROUP BY FIRST_NAME
);

In the preceding query, we are using the subquery for the GREATEST function on the MIT_STUDENTS table. As the outer query says, we will retrieve all records from the MIT_STUDENTS table.

In the INNER query, we have selected records from the FIRST_NAME column and used the GREATEST function on the MATHS, PHYSICS, and COMPUTER_SCIENCE columns to give the highest value as per rows of the MIT_STUDENTS table.

And we have grouped them in the FIRST_NAME column by using the GROUP BY clause on the MIT_STUDENTS table. This process is working inside the INNER query.

Read: MariaDB Max Connections

MariaDB Greatest Year

In this section, we will use the MariaDB GREATEST function with the YEAR function in the query and which is explained with the help of an illustrated example.

In MariaDB, the YEAR function is used to calculate the year from the DATE column in the query. Let’s see an illustrated example of the GREATEST function with the YEAR function by the following query:

EXAMPLE:

SELECT GREATEST(YEAR(JOINING_DATE),YEAR(EXPIRING_DATE)) AS GREATEST_DATE FROM FAZZRO_COMPANY 
LIMIT 5;

• We utilized the YEAR function to calculate the YEAR value on the JOINING_DATE and EXPIRING_DATE columns in this query, which is then used inside the GREATEST function.
• Using the GREATEST function, we computed the greatest year from the JOINING_DATE and EXPIRING_DATE columns.
• We utilized the alias clause with the AS keyword and name for the outcome column’s GREATEST_DATE, which has been taken from the FAZZRO_COMPANY table while using the SELECT statement.
• And to shorten the term of the GREATEST function with the YEAR function.

You may also like to read the following MariaDB tutorials.

• What is MariaDB Sharding
• Add Data To Table MariaDB
• MariaDB Display List of Databases
• MariaDB Date Greater Than
• MariaDB Check Constraint
• MariaDB Rename View
• MariaDB Rename Index
• MariaDB JSON Function
• MariaDB Insert Into Select

So, in this tutorial, we have learned how to use the MariaDB Greatest function. Additionally, we have also covered the following set of topics.

• MariaDB Greatest
• MariaDB Greatest Null
• MariaDB Greatest Date
• MariaDB Greatest-N-Per-Group
• MariaDB Greatest Join
• MariaDB Greatest Year