SQLite max() Function

The SQLite max() function calculates the maximum value of all specified values ​​in a group and returns it.

Syntax

Here is the syntax of the SQLite max() function:

max(expr)

Parameters

expr

Required. A column name or expression to compute the maximum value.

Return value

The SQLite max() function returns the maximum value calculated for all specified values ​​in a group.

Examples

To demonstrate the usages of max(), we simulate a temporary set with the following UNION statement:

SELECT 'Tim' name, 'Math' subject, 8 'mark'
UNION
SELECT 'Tim' name, 'English' subject, 9 'mark'
UNION
SELECT 'Tom' name, 'Math' subject, 7 'mark'
UNION
SELECT 'Tom' name, 'English' subject, 5 'mark';

name  subject  mark
----  -------  ----
Tim   English  9
Tim   Math     8
Tom   English  5
Tom   Math     7

Here, we have some rows for marks of students, and in each row is a student’s mark for one subject.

To get the highest mark of each student, use the following statement:

SELECT
    t.name,
    max(t.mark) 'average marks'
FROM (
    SELECT 'Tim' name, 'Math' subject, 8 'mark'
    UNION
    SELECT 'Tim' name, 'English' subject, 9 'mark'
    UNION
    SELECT 'Tom' name, 'Math' subject, 7 'mark'
    UNION
    SELECT 'Tom' name, 'English' subject, 5 'mark'
) t
GROUP BY t.name;

name  average marks
----  -------------
Tim   9
Tom   7

According the statement, SQLite divides all rows into two groups by name first, and get the maximum mark in each group.