SQLite MAX Function
To fetch the lowest value from an expression, the SQLite MAX function is used.
Syntax 1:
SELECT MAX(aggregate_expression) FROM tables WHERE conditions;
Syntax 2: With GROUP BY clause
SELECT expression1, expression2, ... expression_n MAX(aggregate_expression) FROM tables WHERE conditions GROUP BY expressions;
Example 1:
TEACHERS Table:
ID NAME SALARY SUBJECT 1 Jim 10000 Geology 2 John 20000 Geology 3 Watson 15000 Physics 4 Holmes 25000 Chemistry 5 Tony 30000 Physics
SELECT MAX(SALARY) AS "MAX SALARY" FROM TEACHERS; |
Output:
MAX SALARY 30000
Explanation:
In the above example, we are calculating the maximum salary from the SALARY column of the TEACHERS table.
Example 2:
TEACHERS Table:
ID NAME SALARY SUBJECT 1 Jim 10000 Geology 2 John 20000 Geology 3 Watson 15000 Physics 4 Holmes 25000 Chemistry 5 Tony 30000 Physics
SELECT SUBJECT, MAX(SALARY) AS "MAX SALARY" FROM TEACHERS GROUP BY SUBJECT; |
Output:
SUBJECT MAX SALARY Geology 20000 Physics 30000 Chemistry 25000
Explanation:
In the above example, we are calculating the maximum salary from the SALARY column of the TEACHERS table for each unique group where grouping is done by the SUBJECT Column.