SQLite MIN

SQLite is a serverless database engine with almost zero or no configuration headache and provides a transaction DB engine. SQLite provides an in-memory, embedded, lightweight ACID-compliant transactional database that follows nearly all SQL standards. Unlike other databases, SQLite reads and writes the data into typical disk files within the systems. We can use SQLite in our C/C++/Python programs using the SQLite3 API/library.

What is the MIN Function in SQLite?

The SQLite MIN() function is used to get the smallest value from a set of values of the selected attribute/column. It is an aggregate function often used within the subquery or with GROUP BY and HAVING clauses. Some other SQLite aggregate functions are MAX(), AVG(), SUM(), COUNT().

Syntax:

MIN( [ ALL | DISTINCT ] [<column-name> | <expression>]);

By default, the MIN() function considers ALL the values except the NULL values of the expression to find the minimum value. So, it returns a minimum value from a set of NON-NULL values of an expression/column. The MIN() function will return a NULL value only if all the values of the expression/column are NULL.

Let us consider the following table for our example.

CREATE TABLE GFGCourses ( 
     id INTEGER NOT NULL PRIMARY KEY,    
     course_name TEXT NOT NULL,
     price INTEGER NOT NULL,    
     ratings REAL DEFAULT 0
);

The below tuples are present in the GFGCourses schema specified above.

id	course_name	price	ratings
1	Self-Paced DSA	7999	4.7
2	ML in Python	4999	3.9
3	Maths & CP	6999	4.6
4	Interview Prep	9999	4.9
5	Android Dev	2999	4.0

Output:

Tuples in GFGCourses table

When we use the MIN() function, the default returned column will be named MIN(<column-name>).

SELECT MIN(price) FROM GFGCourses;

Output:

MIN(price)
2999

SELECT MIN(price) FROM GFGCourses

If you want to override or give a new name to the column, use the AS keyword.

SELECT MIN(price) AS MinimumPrice FROM GFGCourses;

Output:

MinimumPrice
2999

SELECT MIN(price) AS MinimumPrice FROM GFGCourses

SQLite MIN() Function Examples

1. MIN() in the Subquery

Query: Find the course(s) whose price is lowest. Output its name and ratings.

SELECT g1.course_name AS CourseName, g1.ratings AS Ratings FROM GFGCourses g1 
WHERE g1.price = (SELECT MIN(g2.price) FROM GFGCourses g2);

Output:

MIN() function usage in subquery

Explanation: The query has to print the details of the course(s), like course name ratings whose price is minimum. We will first find the minimum price among all courses in the inner subquery to achieve that. Now, in the outer subquery, we print the details of course whose price matches the minimum price returned by the inner subquery. For our example, the inner subquery returns 2999. Now, for each tuple, the outer query tries to match the price as 2999. But it only matches for the course ‘Android Dev’.

2. MIN() and GROUP BY Clause

Let us consider another table for this example. Let’s consider Employee table with the following columns and tuples in it.

Data in Employee Table

Query: Find the minimum salary for each location from the Employee table.

SELECT location, MIN(salary)  FROM Employee GROUP BY location;

Output:

MIN() with GROUP BY clause

Explanation:

The query asks to find minimum salary for each location, so we have to first group all the rows using location attribute. Now to report the minimum salary for each location we use the MIN() aggregate function on the salary column. In this way first grouping by location and then selecting location, MIN(salary) gives us the required output.

3. MIN() and HAVING Clause

Query: Write a query that prints the location and number of employees working at that location from the Employee table such that minimum salary of any employee from that location is greater than 1500.

SELECT location AS JobLocation, COUNT(*) AS Count FROM Employee GROUP BY location HAVING MIN(salary) > 1500;

Output:

MIN() with HAVING clause

Explanation:

The query asks to find output for each location which indicates we have to use the GROUP BY clause. So, we will GROUP BY clause on location. Now the query also says that the minimum salary of any employee of a particular location must be greater than 1500. This indicates to use HAVING clause on MIN(salary). This means if the MIN(salary) of any employee at that location is less or equal to 1500 then that location will not be present in the output. And so we will report the location and total number of employees working at that location.

Conclusion

So, the MIN() function is an aggregate function provided in SQLite. It accepts a column/expression as an input and finds and returns the minimum values among the set of values. The MIN() function does not consider NULL values and hence returns NON-NULL minimum value. Also, the default returned column will be named MIN(<column-name>). As seen in the examples above, we can use MIN() within a subquery, and with GROUP BY and HAVING clause.