SQLite min() Function

The SQLite min() function computes the minimum value of all specified values ​​in a group and returns it.

Syntax

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

min(expr)

Parameters

expr

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

Return value

The SQLite min() function returns the minimum value of all specified values ​​in a group.

Examples

To demonstrate the usages of min(), 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 lowest mark of each student, use the following statement:

SELECT
    t.name,
    min(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   8
Tom   5

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