SQLite MAX() Function

MAX function is a type of Aggregate Function available in SQLite, which is primarily used to find out the maximum value from a given set (a column that is passed as its parameter). Other than that, the MAX function can also be used with other Aggregate functions like HAVING, GROUP BY, etc to sort or get some values that are obeying the condition mentioned by these other Aggregate Functions.

In this article, we will see how we can use the MAX function to do various tasks and how the MAX function can be used in various ways to get more precise outputs from the table.

SQLite MAX Function

As mentioned earlier, the SQLite MAX function can be used in various ways based on the requirements of the user. If MAX is used all alone then it can used to fetch the maximum value from the set or the column passed as its parameter. When the MAX function is used with other Aggregate Functions like HAVING or GROUP BY it can be manipulated by the user as their requirement. The generic syntax of the MAX function is given below –

Syntax:

MAX (COLUMN_NAME | EXPRESSION) FROM TABLE_NAME [WHERE CONDITION] [GROUP BY EXPRESSION]

Explanation

• First, we need to pass the Column_Name or an Expression in the first parenthesis. Expression is passed when MAX is used with other Aggregate Functions like GROUP BY, if MAX is used alone, then COLUMN_NAME will be passed.
• Secondly, we need to tell the MAX function to work on a certain table by passing its name using FROM. Now if MAX is used alone, then that would be the end of the statement. But if it is used with others then we can also pass some condition using WHERE or some Expression while using GROUP BY. The WHERE condition and the GROUP BY Expression are Optional.

Creating and Populating SQLite Table

We will use the CREATE TABLE command to create the table and provide the column names and their data types.

 CREATE TABLE Employees (
(x1...> empID INTEGER,
(x1...> FirstName TEXT,
(x1...> LastName TEXT,
(x1...> Salary INTEGER,
(x1...> Location TEXT
(x1...> );

Here, we will use a Table Called Employees which will have the following columns and with their datatype –

• empID – It is of type Integer.
• FirstName = It is of type Text.
• LastName – It is of type Text.
• Salary – It is of Type Integer.
• Location – It is of Type Text.

Output:

Adding values into the Table using the INSERT INTO command.

Query:

INSERT INTO Employees VALUES(1,'Sonia','Wong',20000,'AL');
INSERT INTO Employees VALUES(2,'Neel','Lee',25000,'FL');
INSERT INTO Employees VALUES(3,'Melody','Abott',23000,'IA');
INSERT INTO Employees VALUES(4,'Trinity','Kirk',21000,'IL');
INSERT INTO Employees VALUES(5,'Miley','Webster',28000,'IN');
INSERT INTO Employees VALUES(6,'Sydnee','Donaldson',27000,'KY');
INSERT INTO Employees VALUES(7,'Matilda','Roach',35000,'MN');
INSERT INTO Employees VALUES(8,'Chanel','Mcneil',33000,'MI');
INSERT INTO Employees VALUES(9,'Gilberto','Blake',34000,'MS');
INSERT INTO Employees VALUES(10,'Harmony','Serrano',32000,'NV');

After Inserting the values into the table, we will print all the values using the SELECT command to see if everything is fine or not

SELECT * FROM Employees;

Output:

Now we are ready to use MAX functions and get our desired results.

SQLite MAX Function in the Subquery

In this example, we will see how we can use the MAX function in a subquery to print the FirstName, LastName , and Salary of the Employee having the Maximum Salary. The generic Syntax while using the MAX function alongside Subquery is as follows –

SELECT
(Col1, Col2, Col3, ......, ColN)
FROM
Table_Name
WHERE
Column_Name = (SELECT MAX(Column_Name) FROM Table_Name);

The above syntax signifies how we can use MAX statement inside of a SubQuery to get our desired result

SELECT FirstName, LastName, Salary FROM Employees WHERE Salary = (SELECT MAX(Salary) FROM Employees);

Output:

The Output prints the FirstName, LastName and the Salary of that person who has the highest salary. In the query we are using a Subquery with the MAX function to get the maximum salary.

SQLite MAX Function and GROUP BY Clause

The GROUP BY function is used to Group Together rows based on certain conditions. GROUP BY statement is generally used with other Aggregate functions like COUNT(), MAX(), MIN(), SUM(), and AVG(). We will see here how we can use MAX function along with GROUP BY function. The generic syntax is below –

SELECT
(Col1,Col2,Col3,.......,ColN)
FROM
Table_Name
GROUP BY
Column_Name

The Column used after GROUP BY should be used in the SELECT statement above, otherwise it will not work. For Example, we will use the MAX with the GROUP BY clause to print out the order of the maximum salary of each employee, regardless of their employee Id or anything else except their salary.

SELECT empID, FirstName, LastName, MAX(Salary) AS Maximum_Salary FROM Employees GROUP BY Salary;

Output:

In the output , we can clearly see that the Salary has been sorted from lowest to highest without considering anything else like FirstName , LastName, etc. The Salary has been GROUPED BY based on their amount.

SQLite MAX Function and HAVING Clause

MAX function can also be used with the HAVING clause. HAVING clause will be used after the GROUP BY statement, so it is necessary to use GROUP BY in the statement in which we will use HAVING. The generic syntax is as follows –

SELECT
    Col1,Col2,Col3,.......,ColN
FROM
    Table_Name
GROUP BY
    Column_Name
HAVING Condition / Expression;

We will print the Employee ID, FirstName and LastName of only those employees, whose salary is greater than 25,000. We will pass this condition after the HAVING clause.

SELECT empID, FirstName, LastName, MAX(Salary) AS Maximum_Salary FROM Employees GROUP BY Salary HAVING MAX(Salary) > 25000;

Output:

In the output we can clearly see only the details of those employees having a salary more than 25,000 have been grouped based on their salary from lowest to highest.

Conclusion

The MAX Aggregate Function is a very useful tool for SQLite3 developers as it helps in finding many things like the MAX value present in a certain column, which might be very useful to know for certain tables and databases. Other than that the MAX function can work together smoothly with other Aggregate Functions like HAVING, GROUP BY etc. which makes it more powerful in finding and returning more accurate results based on certain conditions.