SQLite MAX() Function

Here we will learn what is SQLite Max() function and how to use SQLite Max() function with WHERE, Group By, Having to get maximum value from a column with examples.

SQLite MAX() Function

In SQLite MAX() Function is an aggregate function that is used to get the maximum value of specified expression or column.

 

Generally, in SQLite MAX() function will work with non-NULL numeric values to return the maximum value of a column in a table. In case if the table column contains only NULL values means MAX() function will return NULL.

Syntax of SQLite Max() Function

Following is the syntax of the SQLite max() function to get the maximum value of a defined expression.

 

Select MAX(Expressionfrom tablename

[WHERE condition]

[GROUP BY Expression];

In above SQLite MAX() function syntax we defined multiple parameters those are

 

Expression – Its column or expression which we used to get the maximum value of defined column or expression.

 

Tablename – Its name of the table which we wish to retrieve records from.

 

[WHERE Condition] – If we have any specific conditions to get the maximum value of column then we need to specify WHERE based on our requirements. It’s optional.

 

[Group By Expression] – If we want to get maximum value based on particular Expression then we need to use Group By. It’s optional.

Example of SQLite MAX() Function

Now we will see how to use MAX() function in SQLite to get the maximum value of an expression with examples for that we need to create two tables called dept_master and emp_master using the following query.

 

To create and insert some data in dept_master table execute following query.

 

CREATE TABLE dept_master

(dept_id INTEGER PRIMARY KEY AUTOINCREMENT,

dept_name TEXT);

 

INSERT INTO dept_master(dept_name)

VALUES('Admin'),

('Sales'),

('Quality Control'),

('Marketing');

Once we create and insert data execute following query to see the data of dept_master table. 

 

sqliteSELECT FROM dep_master;

 

dept_id     dept_name

----------  ----------

1           Admin

2           Sales

3           Quality Control

4           Marketing

Same way execute the following queries to create and insert some data in the emp_master table.

 

CREATE TABLE emp_master

(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,

first_name TEXT,

last_name TEXT,

salary NUMERIC,

dept_id INTEGER);

 

INSERT INTO emp_master

values (1,'Honey','Patel', 10100,1),

(2,'Shweta','Jariwala', 19300,2),

(3,'Vinay','Jariwala', 35100,3),

(4,'Jagruti','Viras', 9500,2),

(5,'Shweta','Rana',12000,3),

(6,'sonal','Menpara', 13000,1),

(7,'Yamini','Patel', 10000,2),

(8,'Khyati','Shah', 50000,3),

(9,'Shwets','Jariwala',19400,2);

Now run the following query to check the records of emp_master table.

 

sqliteSELECT FROM emp_master;

 

emp_id      first_name  last_name   salary      dept_id

----------  ----------  ----------  ----------  ----------

1           Honey       Patel       10100       1

2           Shweta      Jariwala    19300       2

3           Vinay       Jariwala    35100       3

4           Jagruti     Viras       9500        2

5           Shweta      Rana        12000       3

6           Sonal       Menpara     13000       1

7           Yamini      Patel       10000       2

8           Khyati      Shah        50000       3

9           Shwets      Jariwala    19400       2

Now write the query like as shown following to get the maximum salary of employees from the emp_master table.

 

sqliteSELECT MAX(salaryFROM emp_master;

 

MAX(salary)

-----------

50000

SQLite MAX() Function with Group By Clause

Suppose in SQLite if we want to get the maximum value of an expression based on the group like calculating the maximum salary of each department then we need to use SQLite MAX() function with Group By clause to return maximum value based on our requirements.

 

Following is the SQLite query to use MAX() function with Group By clause to get the maximum salary of each department.

 

sqliteSELECT d.dept_name,max(e.salary),e.first_name

FROM emp_master e, dept_master d

WHERE e.dept_id d.dept_id

GROUP BY e.dept_id;

 

dept_name         max(e.salary)  first_name

----------        -------------  ----------

Admin             13000          Sonal

Sales             19400          Shwets

Quality Control   500000         Khyati

If you observe above query we got maximum salary of each department by using Group By clause with SQLite MAX() function.

SQLite MAX() Function with Having Clause

Suppose if we want to get the maximum value of particular group alone then by using Having clause with SQLite MAX() function we can easily get the required result.

 

Following is the SQLite query which will return maximum salary of each department whose department name starts with “sa”.

 

sqliteSELECT d.dept_name,MAX(e.salary),e.first_name

FROM emp_master e, dept_master d

WHERE e.dept_id d.dept_id

GROUP BY e.dept_id

HAVING d.dept_name LIKE 'sa%';

 

dept_name   max(e.salary)  first_name

----------  -------------  ----------

Sales       19400          Shwets

The above example returns maximum salary of each department whose department name starts with “sa”.

 

This is how we can use SQLite MAX() function to get the maximum value of a column in a table based on our requirements.