Which Data Type Cannot Be Used With MAX And MIN In SQL?

In SQL, the MAX and MIN aggregate functions cannot be used with large object data types like BLOB, TEXT, IMAGE, NTEXT, VARCHAR(MAX), and NVARCHAR(MAX). Support for other specialized or non-standard types can also be limited, including BIT in some implementations like older versions of Microsoft SQL Server, spatial data types, and JSON types depending on the database system.

Detailed Article: Understanding SQL Data Type Limitations with MAX and MIN

While MIN and MAX are among the most fundamental SQL aggregate functions, their application is not universal. The core reason these functions fail on specific data types is that these types often contain data too large or too complex to have a simple, unambiguous "minimum" or "maximum" value. These restrictions vary slightly across different SQL database management systems (DBMS), but a few categories of data types are almost always unsupported.

Primary Unsupported Data Types

Large Object (LOB) Data Types

Large Object (LOB) data types are designed to store massive amounts of unstructured data, and are the most common cause of MAX and MIN errors.

TEXT and NTEXT: These are used to store large character strings. For example, a column holding an entire article or a book chapter is stored as TEXT. Trying to find the "minimum" or "maximum" of such large, non-standardized text blocks is not a meaningful operation.
BLOB and IMAGE: These are used for storing large binary data, such as entire files, images, audio clips, or serialized objects. Like with TEXT, determining the MIN or MAX of a binary file is not a practical or supported operation.
VARCHAR(MAX) and NVARCHAR(MAX): These are variable-length string types that can store very large text data, similar in function to TEXT in modern SQL systems. When a string in these columns exceeds a certain length (e.g., 8,000 characters in SQL Server), it is stored "off-row" as a large object, which prevents aggregation functions like MAX and MIN from working.

Specialized and Complex Data Types

Many modern database systems support specialized data types for complex, non-scalar information. MAX and MIN generally cannot be used on these types because there is no clear ordering to the data.

JSON: The JSON data type stores structured data, not a single value. While you can query individual properties within a JSON document, attempting to find the MIN or MAX of an entire JSON column is unsupported because there is no logical way to compare one complete JSON object to another.
Spatial Data Types: Types like GEOGRAPHY or GEOMETRY in SQL Server store spatial data representing geographic locations, lines, or shapes. There is no simple single "largest" or "smallest" spatial object in a set.
hierarchyid (SQL Server): This data type represents a position in a hierarchy. While a path can be compared to another path, finding a single MIN or MAX from a set of hierarchyid values is not a supported aggregate operation.
XML: Like JSON, XML stores structured data. An entire XML document cannot be evaluated for a single MIN or MAX value.

The `BIT` Data Type (Database-specific)

Some database systems have idiosyncratic restrictions. A notable historical example is Microsoft SQL Server, where the MIN and MAX functions on BIT columns were explicitly not supported in older versions. In this case, there is a clear logical ordering (0 vs. 1), but the functionality was simply not included by the developers for various implementation reasons. Many systems now support this functionality, but it serves as an example of database-specific limitations.

Why Are These Restrictions in Place?

These limitations are not arbitrary. They are a logical consequence of how databases handle and store different kinds of data.

No Clear Ordering: Many complex data types, such as JSON and spatial types, do not have a natural, universally accepted sort order. For example, is a large, complex JSON object "greater than" a small, simple one? Is one polygon "larger than" another? There is no standard way to define this, so MIN and MAX are not supported.
Performance and Storage Overhead: Large objects (BLOB, TEXT, VARCHAR(MAX)) are often stored separately from the rest of the row data. The performance overhead of loading and comparing these massive data blocks for every row in a table would be prohibitive. Instead of performing a "full table scan," the database would have to perform a "full LOB scan," which is a very expensive operation.
Logical Impossibility: As with complex types, it is logically impossible to define a "maximum" or "minimum" binary large object. The database engine simply does not have a method to compare a file full of bytes to another file full of bytes in a meaningful way for this kind of aggregation.

The Workaround: How to Find the Max or Min of Unsorted Data

If you genuinely need to perform a MAX or MIN operation on an unsupported data type, you must first convert the data into a type that is supported.

Using CAST or CONVERT: The most common approach is to CAST the data to a comparable format, such as a string, date, or numeric value. This is useful for finding the earliest or latest date stored as a VARCHAR, for example.
Length as a Proxy: If the goal is to find the largest or smallest LOB by size, you can find the MAX or MIN of the column's length instead. This is particularly useful for finding the largest image or text block in a table.

Example: Finding the largest text entry by length

-- This will fail in many systems
-- SELECT MAX(TextContent) FROM Articles;
-- This is the correct workaround
SELECT MAX(DATALENGTH(TextContent)) FROM Articles;

Use code with caution.

Example: Finding the earliest date stored as a string

-- Assuming DateString is 'YYYY-MM-DD' format
SELECT MIN(CAST(DateString AS DATE)) FROM Events;