MAX

MySQL

MAX(expression)

• expression: The input data, typically set of values or a column, from which the maximum value is to be calculated. It can come from a row or a field in a SQL table. The expression is mandatory and normally refers to a column of a table. The expression must be numeric or date/time based. The SQL MAX() function doesn’t take any string based comparison.

Example

SELECT MAX(Salary)
FROM Employees;

Output

+-------------+
| MAX(Salary) |
+-------------+
|      20000  |
+-------------+

Explanation

The given SQL query selects the highest salary from the ‘Employees’ table. The output shows the highest salary, which is ‘20000’.

PostgreSQL

MAX(expression) OVER ([partition_by_clause] order_by_clause)

• expression: This represents the column or expression on which the MAX function is to be applied. It could be a column in a table, the result of another function, or any standalone value.
• partition_by_clause: This part divides the data into smaller groups or partitions based on the column values specified in this clause. The MAX function will then be applied independently to each of these partitions.
• order_by_clause: This clause orders the data in either ascending or descending order. When this is specified along with the MAX function, it enables the function to return maximum values of a column based on this sort order, rather than based on the entire column or partition.

Example

SELECT MAX(salary)
FROM employees;

Output

  max
-------
 100000
(1 row)

Explanation

The MAX() function is used to return the maximum value of the selected column. In the given example, MAX(salary) returns the highest salary in the employees table.

SQL Server

MAX( [ ALL | DISTINCT ] expression )

• all: This optional parameter includes all values in the calculation, even if there are duplicate values in the expression.
• distinct: This optional parameter ensures that each unique value is considered only once during the calculation of the maximum value.
• expression: This mandatory parameter represents the field, calculation or constant that the MAX function will act upon. The specified expression must result in a value that can be numerically assessed in order to determine the maximum.

Example

SELECT MAX(Salary) AS MaximumSalary
FROM Employees;

Output

MaximumSalary
125000

Explanation

In the example provided, the MAX() function is used to find the highest salary in the ‘Salary’ column of the ‘Employees’ table. The AS keyword is used to rename the column output as ‘MaximumSalary’.

Oracle

MAX( [DISTINCT] expression )

• distinct: This parameter is used to evaluate the maximum value across distinct expressions. DISTINCT ignores duplicate values in the data set and only considers unique values while evaluating the maximum value.
• expression: This is a column or a set of columns in a table. “Expression” can be a single entity like a single column, function, or literal, or it can also be an operation involving one or more values, functions, literals, and columns. This parameter provides the data from which the maximum value is to be derived.

Example

SELECT MAX(salary) AS "Highest Salary" FROM employees;

Output

HIGHEST SALARY
--------------
   100000

Explanation

The SQL code searches through the ‘salary’ column in the ‘employees’ table and retrieves the maximum value, in this case, 100000. This value is then displayed with the column name as “Highest Salary.”

SQLite

MAX( expression )

• expression: This parameter defines an expression or a column name for which maximum value is to be calculated. It can include the name of the column, calculation operations, or any valid SQLite expression.

Example

CREATE TABLE Sales (
    ID INT PRIMARY KEY,
    Quantity INT
);

INSERT INTO Sales (ID, Quantity) VALUES
(1, 10),
(2, 20),
(3, 30),
(4, 40);

SELECT MAX(Quantity) AS MaxQuantity FROM Sales;

Output

MaxQuantity
40

Explanation

The MAX function is used to return the maximum value of the specified column. In this example, MAX returns the highest Quantity in the Sales table.