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.