In this post, we will discuss the similarities and differences between two Azure SQL functions, MAX and GREATEST. Using both functions, we get the largest value out of all the values. They do, however, work in different ways.
The MAX function accepts just one argument, but the GREATEST function requires a columns values or a list of comma separated expressions to compare.
If extra columns, in addition to the column given as an input for the MAX function, must be grouped. Outside of the GREATEST function, additional columns will be permitted and do not need to be grouped. MAX function can be used with OVER clause but not with GREATEST.
Please use the below dataset for practice.
File: Sales.csv
ProductID,2018,2019,2020,2021
P011,151,100,50,77
P012,122,101,44,59
P043,100,65,62,41
P049,121,88,22,80
The following commands were used to create the table and insert the data into it.
CREATE TABLE Sales(
ProductID VARCHAR(20),
Y2018 INT,
Y2019 INT,
Y2020 INT,
Y2021 INT)
INSERT INTO Sales VALUES
(‘P011’,151,100,50,77),
(‘P012’,122,101,44,59),
(‘P043’,100,65,62,41),
(‘P049’,121,88,22,80)
Examples:
SELECT MAX(Y2020) FROM Sales;
This will return the highest salary from the “emp” table.
SELECT GREATEST(Y2020) FROM Sales;
Since the GREATEST function requires at least two parameters, the above will result in an error.
SELECT GREATEST(Y2020,Y2021) FROM Sales;
The preceding will return the highest values from the supplied two columns.
SELECT MAX(Y2020,Y2021) FROM Sales;
Since the MAX function only accepts one parameter, the above will result in an error.
As stated earlier, if extra columns, in addition to the column given as an input for the MAX function, must be grouped. Outside of the GREATEST function, additional columns will be permitted and do not need to be grouped.
SELECT ProductID, MAX(Y2020) FROM Sales GROUP BY ProductID
SELECT ProductID, GREATEST(Y2020, Y2021) FROM Sales
Happy learning!!
Big Data and SQL 
2 comments