A Quick Look at SQL Server’s MIN() Function

by

The MIN() function in SQL Server returns the smallest value from a set of rows. It’s commonly used to find earliest dates, lowest prices, or in general the minimum of any column. While the function itself is simple, you may encounter it written with options like DISTINCT, ALL, or as a window function with OVER(). Some of these options don’t actually change the result in SQL Server but exist for standards compatibility, so it’s worth understanding what they mean if you ever see them in code.

Let’s take a look at a few simple examples to see how it works.

Sample Data

Here’s a table of support tickets to use as our working example:

CREATE TABLE SupportTickets (
    TicketID INT PRIMARY KEY,
    Customer VARCHAR(25),
    Severity INT,
    OpenedDate DATE
);

INSERT INTO SupportTickets (TicketID, Customer, Severity, OpenedDate)
VALUES
(1, 'Orion Manufacturing', 2, '2025-06-10'),
(2, 'Evergreen Design', 4, '2025-06-15'),
(3, 'Orion Manufacturing', 1, '2025-06-20'),
(4, 'Skyline Solar', 3, '2025-07-01'),
(5, 'Orion Manufacturing', 2, '2025-07-05'),
(6, 'Evergreen Design', 2, '2025-07-15'),
(7, 'Skipple Design', 1, '2025-08-15');

Severity is rated 1–5, where 1 is the least severe.

Basic Example

To find the lowest severity level across all tickets:

SELECT MIN(Severity) AS LowestSeverity
FROM SupportTickets;

Result:

LowestSeverity
--------------
             1

Pretty simple.

MIN() with DISTINCT

You may sometimes see MIN(DISTINCT col). Unlike other aggregate functions, like COUNT(DISTINCT col) for example, this has no effect. Microsoft’s documentation explicitly states:

“DISTINCT is not meaningful with MIN and is available for ISO compatibility only.”

That means the following two columns return the exact same result:

SELECT 
  MIN(Severity) AS "All",
  MIN(DISTINCT Severity) AS "DISTINCT"
FROM SupportTickets;

Output:

All         DISTINCT   
----------- -----------
          1           1

In our table, we have two rows with a severity level of 1, but duplicates don’t affect what the minimum is. It doesn’t matter how many rows have 1 – it doesn’t change the fact that 1 is still the minimum value. So you can safely ignore DISTINCT when you see it with MIN(). Or you can safely implement it, knowing that it won’t change the results.

MIN() with ALL

The ALL keyword is the default behavior of MIN() (and most other aggregate functions). It simply means “consider all the values”. Since it’s implied, adding it doesn’t change the result:

SELECT MIN(ALL Severity) FROM SupportTickets;

Result:

1

There’s no practical difference from leaving it out, but ALL exists in the syntax for standards compliance and consistency.

MIN() with OVER

The more interesting variation comes with the OVER clause. This turns it into a window function. Instead of collapsing all rows into one, it calculates the minimum over partitions or across the entire dataset, while still returning detail rows.

For example, let’s find the lowest severity for each customer but still show all their tickets.

SELECT
    Customer,
    Severity,
    MIN(Severity) OVER (PARTITION BY Customer) AS CustomerMinSeverity
FROM SupportTickets;

Results:

Customer                  Severity    CustomerMinSeverity
------------------------- ----------- -------------------
Evergreen Design                    4                   2
Evergreen Design                    2                   2
Orion Manufacturing                 1                   1
Orion Manufacturing                 2                   1
Orion Manufacturing                 2                   1
Skipple Design                      1                   1
Skyline Solar                       3                   3

This lets us compare each ticket to the minimum severity for that customer without writing a subquery.

You can also apply OVER() without a partition:

SELECT
    TicketID,
    Severity,
    MIN(Severity) OVER () AS OverallMin
FROM SupportTickets;

Output:

TicketID    Severity    OverallMin 
----------- ----------- -----------
          1           2           1
          2           4           1
          3           1           1
          4           3           1
          5           2           1
          6           2           1
          7           1           1

Every row now includes the global minimum, 1.

Wrapping Up

The MIN() function is quite a straightforward function, and the extra options can make it feel more complicated than it really is:

  • DISTINCT doesn’t change the result at all – it’s just for ISO compliance.
  • ALL is the default, so including it is redundant.
  • OVER() makes MIN() much more flexible, letting you find minimums per group (or overall) without collapsing your result set.