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.
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.
Following is the syntax of the SQLite max() function to get the maximum value of a defined expression.
Select MAX(Expression) from 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.
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.
sqlite> SELECT * 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.
sqlite> SELECT * 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.
sqlite> SELECT MAX(salary) FROM emp_master;
MAX(salary)
-----------
50000
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.
sqlite> SELECT 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.
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”.
sqlite> SELECT 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.