SQL MIN Function

The SQL MIN function is used to find the minimum value in a column. It’s commonly used with numeric, date, or text data types and returns the smallest value from a specified column.

The MIN function is often used in conjunction with the GROUP BY clause to get minimum values for each group in the dataset.

In this tutorial, we will go through SQL MIN Function, its syntax, and how to use this function in SQL statements, with the help of well detailed examples.

Syntax of SQL MIN Function

The basic syntax of the SQL MIN function is as follows:

</>
Copy
SELECT MIN(column_name) AS min_value
FROM table_name
[WHERE condition];

Each part of this syntax has a specific purpose:

  • SELECT: Specifies the column to retrieve the minimum value from.
  • MIN(column_name): The MIN function, which returns the smallest value in the specified column.
  • AS min_value: An alias for the minimum value column in the results.
  • FROM: Specifies the table from which to retrieve data.
  • WHERE (optional): Filters the data based on specified conditions before applying the MIN function.

Step-by-Step Examples with MySQL

We’ll go through various examples demonstrating the MIN function in MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use a sample students table with fields id, name, age, grade, and locality along with a grades table that records students’ scores.

Setup for Examples: Creating the Database and Tables

1. Open MySQL Workbench and create a new database:

</>
Copy
CREATE DATABASE school;

2. Select the school database:

</>
Copy
USE school;

3. Create a students table:

</>
Copy
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(10),
    locality VARCHAR(50)
);

4. Create a grades table:

</>
Copy
CREATE TABLE grades (
    student_id INT,
    subject VARCHAR(50),
    score INT,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

5. Insert sample data into the students and grades tables:

</>
Copy
INSERT INTO students (name, age, grade, locality)
VALUES
('Alice', 14, '8th', 'Northside'),
('Bob', 15, '9th', 'Westend'),
('Charlie', 14, '8th', 'Northside'),
('David', 16, '10th', 'Southend'),
('Eva', 15, '9th', 'Westend');

INSERT INTO grades (student_id, subject, score)
VALUES
(1, 'Math', 85),
(2, 'Math', 78),
(3, 'Science', 90),
(4, 'Math', 88),
(5, 'Science', 70);