MySQL MIN(): Find the Lowest Value in a Column

MySQL MIN() returns the lowest non-NULL value produced by an expression. It works with numeric values, dates, and text.

SELECT MIN(mark) AS min_mark
FROM student;

For the sample rows below, the minimum mark is 55.

MIN() Syntax Top ↑

SELECT MIN(expression)
FROM table_name;

MIN() ignores NULL values. If no non-NULL value is available, the result is NULL.

Basic MIN() Example Top ↑

The examples use these rows from the student table:

SELECT MIN(mark) AS min_mark
FROM student;

See MySQL MAX() for the corresponding highest-value query.

Get the Complete Row with the Minimum Value Top ↑

This is a common requirement: find the lowest mark and also return the student or students who have that mark.

Do not write:

-- Wrong or invalid for this purpose
SELECT id,
       name,
       class,
       MIN(mark) AS min_mark
FROM student;

With ONLY_FULL_GROUP_BY enabled, MySQL rejects this because the non-aggregate columns are not grouped. With permissive settings, those columns can come from an arbitrary row and are not guaranteed to belong to the minimum mark.

Use a subquery:

SELECT id,
       name,
       class,
       mark
FROM student
WHERE mark = (
    SELECT MIN(mark)
    FROM student
)
ORDER BY id;

With the sample data, two students have the minimum mark of 55:

Return one lowest row only Top ↑

If the requirement is specifically to return one row, sort ascending and use a deterministic tie-breaker:

SELECT id,
       name,
       class,
       mark
FROM student
ORDER BY mark, id
LIMIT 1;

This deliberately chooses the lowest id when several rows share the same minimum mark.

What Happens When Several Rows Share the Minimum? Top ↑

A condition such as:

WHERE mark = (
    SELECT MIN(mark)
    FROM student
)

returns all tied rows. This is usually the correct behavior when the question is "Which students have the lowest mark?"

If the application needs exactly one row, define an explicit tie-breaker rather than assuming the minimum value is unique.

Minimum Value in Each Group Top ↑

Use GROUP BY to calculate the minimum mark for each class:

SELECT class,
       MIN(mark) AS min_mark
FROM student
GROUP BY class
ORDER BY class;

This returns one aggregate result per class. It does not yet identify the complete student row that produced each minimum.

Complete Row with the Minimum in Each Group Top ↑

Calculate the minimum for each class and join the result back to the source table:

SELECT s.id,
       s.name,
       s.class,
       s.mark
FROM student AS s
JOIN (
    SELECT class,
           MIN(mark) AS min_mark
    FROM student
    GROUP BY class
) AS m
  ON m.class = s.class
 AND m.min_mark = s.mark
ORDER BY s.class, s.id;

This correctly returns tied minimum rows when two students in the same class have the same lowest mark.

MySQL 8.0 window-function alternative Top ↑

WITH ranked AS (
    SELECT id,
           name,
           class,
           mark,
           RANK() OVER (
               PARTITION BY class
               ORDER BY mark
           ) AS rnk
    FROM student
)
SELECT id,
       name,
       class,
       mark
FROM ranked
WHERE rnk = 1
ORDER BY class, id;

RANK() preserves ties. Use ROW_NUMBER() only when the requirement is exactly one row per group and the ORDER BY defines a deliberate tie-breaker.

MIN() with WHERE Top ↑

A WHERE clause filters rows before MIN() is calculated:

SELECT MIN(mark) AS min_mark
FROM student
WHERE class = 'Four';

The minimum mark for class Four is 55.

MIN() with JOIN Top ↑

When related tables are involved, avoid selecting an unrelated product beside MIN(qty) unless the query also identifies which row produced that minimum.

First find the minimum quantity for each store:

SELECT store,
       MIN(qty) AS min_qty
FROM sales
GROUP BY store
ORDER BY store;

To return the product row associated with that minimum quantity, join the grouped result back to sales and then to products:

SELECT s.store,
       s.qty AS min_qty,
       p.product,
       p.price,
       p.price * s.qty AS total_price
FROM sales AS s
JOIN (
    SELECT store,
           MIN(qty) AS min_qty
    FROM sales
    GROUP BY store
) AS m
  ON m.store = s.store
 AND m.min_qty = s.qty
LEFT JOIN products AS p
  ON p.p_id = s.p_id
ORDER BY s.store, s.p_id;

If two sales rows in the same store share the minimum quantity, both are returned.

MIN() as a Window Function Top ↑

A window-function form of MIN() keeps each detail row while adding the minimum for its group:

SELECT id,
       name,
       class,
       mark,
       MIN(mark) OVER (
           PARTITION BY class
       ) AS class_min
FROM student
ORDER BY class, id;

This is useful when the report needs both each student's row and the minimum mark for that class. See OVER() and PARTITION BY.

MIN() on Text Values Top ↑

MIN() can operate on text columns. MySQL compares character values according to the column's collation.

SELECT MIN(name) AS min_name
FROM student;

The result is the lowest text value according to the active collation. This is not a numeric comparison.

Numbers Stored in VARCHAR Top ↑

If numeric-looking values are stored in a VARCHAR column, MIN() compares them as text. Convert them to a numeric type when a numeric comparison is required:

SELECT MIN(
           CONVERT(t1, UNSIGNED)
       ) AS numeric_min
FROM min_value;

For fundamentally numeric data, storing the values in an appropriate numeric datatype is normally better than repeatedly converting text at query time.

See MySQL CONVERT().

MIN() on DATE and DATETIME Top ↑

On a DATE or DATETIME column, MIN() returns the earliest non-NULL value:

SELECT MIN(exam_dt) AS first_exam
FROM student_mark;

First exam date for each month Top ↑

If the table can contain multiple years, grouping only by month number mixes January from different years. Group by year and month:

SELECT YEAR(exam_dt) AS exam_year,
       MONTH(exam_dt) AS exam_month,
       MIN(exam_dt) AS first_exam
FROM student_mark
GROUP BY YEAR(exam_dt),
         MONTH(exam_dt)
ORDER BY exam_year, exam_month;

Download the existing student_mark sample SQL data.

NULL Values and MIN() Top ↑

MIN() ignores NULL values. If all selected values are NULL, or no rows match the filter, the aggregate result is NULL.

SELECT MIN(mark) AS min_mark
FROM student
WHERE id > 1000;

If the application needs a fallback such as zero, use COALESCE() only when that fallback is meaningful:

SELECT COALESCE(
           MIN(mark),
           0
       ) AS min_mark
FROM student
WHERE id > 1000;

See SQL NULL values.

Using MIN() in a PHP PDO Script Top ↑

After connecting to MySQL with PDO, a static grouped MIN query can be executed with query():

<?php
$sql="SELECT class,
             MIN(mark) AS min_mark
      FROM student
      GROUP BY class
      ORDER BY class";

$stmt=$dbo->query($sql);

foreach($stmt as $row){
    echo '<p>'
        .htmlspecialchars(
            $row['class'],
            ENT_QUOTES,
            'Windows-1252'
        )
        .' : '
        .htmlspecialchars(
            (string)$row['min_mark'],
            ENT_QUOTES,
            'Windows-1252'
        )
        .'</p>';
}

This SQL is static, so query() is appropriate. If a filter value comes from user input, use a prepared statement and bind the value.

Using MIN() with Python and SQLite Top ↑

The same aggregate concept works in SQLite. Plus2net's SQLite/Colab example shows the sample database and connection object.

query="SELECT MIN(mark) AS min_mark FROM student"
row=my_conn.execute(query).fetchone()
print(row[0])

The previous version of this page showed a Python LIMIT query in the MIN section. That did not demonstrate MIN(); the example above now matches the SQL concept being taught.

Performance Notes Top ↑

• A simple MIN(indexed_column) query can often benefit from an appropriate index, depending on filters and the optimizer plan.
• If the query filters first, indexes supporting the WHERE condition can matter as much as the MIN expression.
• Grouping by functions such as YEAR(date_col) and MONTH(date_col) can add work on large tables.
• Converting numeric-looking VARCHAR data during every query adds overhead. Use numeric datatypes for numeric data whenever practical.
• Use EXPLAIN and real measurements for performance decisions.

Common MIN() Mistakes Top ↑

Selecting unrelated columns beside MIN() Top ↑

SELECT name, MIN(mark) FROM student does not reliably return the name belonging to the lowest mark. Use a subquery, JOIN, or window function.

Forgetting about ties Top ↑

The lowest value can belong to more than one row. Decide whether all tied rows should be returned or exactly one row with a documented tie-breaker.

Grouping only by month number across several years Top ↑

GROUP BY MONTH(exam_dt) mixes the same month from different years. Include the year when the data spans more than one year.

Using MIN() on numeric values stored as text Top ↑

VARCHAR values are compared as text. Use a numeric datatype or convert the expression before applying MIN().

Assuming NULL is treated as zero Top ↑

MIN() ignores NULL. Replacing NULL with zero changes the meaning of the calculation and should only be done when zero is genuinely the desired fallback.

Using an unrelated Python example Top ↑

A cross-language example should demonstrate the same SQL concept. LIMIT and MIN answer different questions.

Related Tutorials and Downloads Top ↑

Download the full Plus2net student table SQL dump

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: