SQLite | SQLite Functions | Finding Maximum and Minimum Column Values (max and min Functions)

Tags:

• SQLite
• Database

The max function returns the largest value stored in a specified column, while min returns the smallest. This article explains how to use both functions.

Using the max and min Functions

Use max to find the largest value. Its syntax is as follows.

max(column_name)

It returns the largest value stored in the specified column. When a GROUP BY clause is used, it returns the largest value in each group.

Use min to find the smallest value. Its syntax is as follows.

min(column_name)

It returns the smallest non-NULL value stored in the specified column. With a GROUP BY clause, it returns the smallest value in each group.

Both functions return NULL when the specified column contains only NULL values.

–

Let’s find maximum and minimum values. First, create the following table.

create table user (name text, point integer, gender text);

sqlite> create table user (name text, point integer, gender text);
sqlite> 

Add the following data with INSERT statements.

insert into user values ('dekuma', 72, 'Man');
insert into user values ('kimkc', 94, 'Woman');
insert into user values ('araikuma', 58, 'Woman');
insert into user values ('happykuma', 82, 'Man');

sqlite> insert into user values ('dekuma', 72, 'Man');
sqlite> insert into user values ('kimkc', 94, 'Woman');
sqlite> insert into user values ('araikuma', 58, 'Woman');
sqlite> insert into user values ('happykuma', 82, 'Man');
sqlite> 

Use max and min to find the largest and smallest values in the point column.

select max(point), min(point) from user;

sqlite> select max(point), min(point) from user;
max(point)  min(point)
----------  ----------
94          58        
sqlite> 

The query returns the largest and smallest values in the column.

Next, group the rows by gender and find the largest and smallest values in each group.

select gender, max (point), min (point) from user group by gender;

sqlite> select gender, max (point), min (point) from user group by gender;
gender      max (point)  min (point)
----------  -----------  -----------
Man         82           72         
Woman       94           58         
sqlite> 

The GROUP BY clause makes it possible to calculate maximum and minimum values for each group. For details, see Grouping Data to Calculate Totals and Averages (GROUP BY and HAVING Clauses).