Using MAX() in SQL Server

The MAX() function is one of SQL Server’s simplest aggregate functions. It returns the largest value from a column. While it’s usually straightforward, there are a few useful ways to apply it depending on whether you’re using it as a plain aggregate or as a window function with OVER().

You might also see MAX() that use a DISTINCT clause. Truth be told, this doesn’t actually change the results. That clause is only for standards compatibility.

In any case, let’s walk through some examples to see how it all works.

Basic Example

Imagine we have a table called Invoices:

CREATE TABLE Invoices (
    InvoiceID INT PRIMARY KEY,
    ClientName VARCHAR(100),
    Amount DECIMAL(10,2),
    InvoiceDate DATE
);

INSERT INTO Invoices (InvoiceID, ClientName, Amount, InvoiceDate)
VALUES
(1, 'Harborview Capital', 450.00, '2025-01-05'),
(2, 'Sterling Partners', 1200.00, '2025-01-10'),
(3, 'Harborview Capital', 300.00, '2025-02-02'),
(4, 'Pinnacle Equity Group', 1200.00, '2025-02-15'),
(5, 'Harborview Capital', 900.00, '2025-03-01');

If we just want the largest invoice amount across all clients:

SELECT MAX(Amount) AS HighestInvoice
FROM Invoices;

Result:

1200.00

That’s the standard usage most people are familiar with, and it produced the maximum value as expected.

`MAX()` with `DISTINCT`

While MAX() function supports the DISTINCT clause, this is only for ISO compatibility. The DISTINCT clause is kind of irrelevant with this function, given there’s still only one maximum value per query. Even if there are multiple rows with the same maximum value, they are all the same value and so SQL Server will return that one value regardless of how many rows contain it.

In other words, multiple max values don’t change the outcome like we might observe with some other aggregate functions. For example, if we use AVG() to calculate the average across the whole rowset, then DISTINCT could change the results in a meaningful way. Not so with MAX(). The max value is the max value. Other values have no bearing on that.

And to support this notion, Microsoft’s documentation states the following about the DISTINCT clause:

Specifies that each unique value is considered. DISTINCT is not meaningful with MAX and is available for ISO compatibility only.

In any case, here’s an example of usage:

SELECT MAX(DISTINCT Amount) AS HighestDistinctInvoice
FROM Invoices;

Output:

1200.00

The result is still the same, even though it technically removed duplicates (and 1200.00 was a duplicate – our table contained two rows with that value).

`MAX()` with `ALL`

There’s also an ALL keyword that applies the aggregate function to all values (as opposed to distinct values). This is the default, and so there’s no need to use this keyword. Also, given DISTINCT doesn’t change the outcome, there’s not really much use for either keyword, other than to make our code more explicit.

Here’s an example of using the ALL keyword:

SELECT MAX(ALL Amount) AS HighestInvoice
FROM Invoices;

Output:

HighestInvoice
--------------
       1200.00

`MAX()` with `OVER`

The real flexibility comes with the OVER clause, which turns MAX() into a window function. Instead of collapsing the dataset into a single row, it calculates the maximum across a partition while keeping the rows intact.

For example, let’s find the highest invoice amount for each client, but still show all their invoices:

SELECT
    ClientName,
    Amount,
    MAX(Amount) OVER (PARTITION BY ClientName) AS ClientMax
FROM Invoices;

Results:

ClientName                     Amount       ClientMax   
------------------------------ ------------ ------------
Harborview Capital                   450.00       900.00
Harborview Capital                   300.00       900.00
Harborview Capital                   900.00       900.00
Pinnacle Equity Group               1200.00      1200.00
Sterling Partners                   1200.00      1200.00

Here, each row shows the client’s invoice alongside the maximum invoice for that client. No need to group or join.

You could also drop the partition and apply OVER() to the whole dataset:

SELECT
    InvoiceID,
    Amount,
    MAX(Amount) OVER () AS OverallMax
FROM Invoices;

Output:

InvoiceID   Amount       OverallMax  
----------- ------------ ------------
          1       450.00      1200.00
          2      1200.00      1200.00
          3       300.00      1200.00
          4      1200.00      1200.00
          5       900.00      1200.00

Now every row displays the global maximum, 1200.00, next to its amount.

Wrapping Up

The MAX() function is quite a simple function, but we can use it in a few different ways:

Plain MAX() collapses rows into one value.
MAX(DISTINCT col) filters duplicates before calculating, but this doesn’t change the result. The DISTINCT clause is purely for ISO compatibility.
MAX() OVER (...) turns it into a window function, letting you keep row detail while still seeing maximums per partition or overall.

Basic Example

MAX() with DISTINCT

MAX() with ALL

MAX() with OVER

Wrapping Up

`MAX()` with `DISTINCT`

`MAX()` with `ALL`

`MAX()` with `OVER`