Oracle’s MAX function works on character values: it returns the string that sorts highest among the non-NULL values in the input. For example, if product_name contains Amber, Birch, and Cedar, SELECT MAX(product_name) FROM products returns Cedar under the usual binary sort—not the longest string or the most recently added one.
What MAX means for character data
MAX is an aggregate function. It examines the values in a column or expression and returns one maximum value. With strings, “maximum” means the value that ranks highest under the applicable character comparison and collation rules. It does not mean greatest length, last inserted row, or a meaningful business concept such as best or newest.
For a straightforward example, suppose a table has a column called category containing Alpha, Delta, and Gamma. This query returns Gamma under the usual binary ordering:
SELECT MAX(category) AS maximum_category FROM products;
The function returns a value from the input; it does not assemble or transform the strings. Duplicate values do not change the result. The optional DISTINCT keyword is therefore unnecessary for MAX: removing duplicates cannot change which value is greatest.
How Oracle compares strings
String comparisons are based on a collation, which defines the ordering of character values. In a typical Oracle database configuration, binary ordering is used: character codes determine the order. Oracle also supports linguistic collations, which can order text according to language-sensitive rules. The applicable behavior can depend on the column’s declared collation and database or session settings, as well as the Oracle version and configuration.
This distinction matters for accented characters, case, and language-specific sorting. Under one collation, uppercase and lowercase versions may have a particular order; under another, they may compare as equivalent or sort differently. Accented letters can also move relative to unaccented letters. Do not assume that every database will choose the same maximum for mixed-case or accented text. If the exact ordering is important, check the collation used by the column and the settings used by the query, then test with representative values.
In the common binary case, a text maximum can look surprising if you expect dictionary or human-language order. For ordinary values whose first letters are all uppercase, MAX may appear to return the alphabetically last value. That observation is not a guarantee about every character set, case combination, or linguistic collation. The reliable description is “highest under the applicable collation.”
Basic query patterns
Use MAX in a query to find one maximum across a table. The following returns the greatest non-NULL value in last_name:
SELECT MAX(last_name) AS maximum_last_name FROM employees;
You can also apply it to an expression, provided the expression produces a value Oracle can order. For instance, if display_name is built from character columns, the result is compared as a string. Be mindful that concatenation changes the values being compared; the function is finding the maximum expression result, not necessarily the maximum of one source column.
To get one maximum per group, combine the aggregate with GROUP BY. Assuming department_id identifies departments, this query returns the greatest last name in each department:
SELECT department_id, MAX(last_name) AS maximum_last_name FROM employees GROUP BY department_id;
Each output row represents a group, and its maximum is calculated only from that group’s values. Departments without any employee rows do not appear in this grouped result. A group that exists but contains only NULL names returns NULL for its maximum.
String maximum is not numeric maximum
A frequent source of errors is storing numbers or numeric-looking identifiers as text. Oracle does not interpret a character value as a number merely because it contains digits. With the strings 2 and 10, binary string ordering places 2 above 10, because their first characters are compared and the character 2 sorts after 1. Thus, MAX on those strings can return 2, even though ten is numerically greater than two.
For values that represent quantities, use a numeric column or convert valid text to a numeric value before aggregating. Conversion can fail if any input contains nonnumeric text, so clean or validate the data before relying on it. For identifiers such as postal codes, product codes, or account numbers, lexical ordering may be exactly what is wanted; converting them to numbers could remove leading zeroes or otherwise change their meaning.
Text ordering can match numeric ordering when every value is a digit string of the same width, such as 02, 10, and 25, but that is a property of the data format, not a numeric feature of MAX. If the width varies, values such as 9 and 10 show why the distinction matters.
NULLs, empty strings, and spaces
Like other Oracle aggregate functions, MAX ignores NULL values. If a column contains Apple, NULL, and Peach, the result is Peach. If the query has no non-NULL values to consider—for example, the table is empty or every value in the selected group is NULL—the result of the aggregate is NULL.
Oracle treats a zero-length character string, written as '', as NULL for character data. That means an empty string does not act as a separate value that can win or lose a comparison in the way it might in another database system. A string containing one or more spaces is not the same thing as a zero-length string, however. Whitespace can affect comparisons and can make data look blank while still containing characters.
Also consider the declared data type. A CHAR column is fixed-width and is blank-padded to its declared length, whereas VARCHAR2 stores variable-length character values. Padding and the applicable comparison rules can matter when values differ only in trailing spaces. If results involving spaces are unexpected, inspect the stored values and data types rather than judging only by how a client tool displays them.
Getting the row that contains the maximum
SELECT MAX(name) returns the maximum name, not the complete row from which that name came. This is important when you also need an employee number, date, or other column associated with the winning name. Selecting a nonaggregated column alongside MAX without grouping or another valid method can produce a grouping error; adding every other column to GROUP BY may change the result by creating smaller groups.
One option is to rank rows with an analytic function and then filter in an outer query. For example, this pattern orders each department’s names from greatest to least and retains the first row per department:
SELECT department_id, employee_id, last_name FROM (SELECT department_id, employee_id, last_name, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY last_name DESC) AS rn FROM employees) WHERE rn = 1;
Here, ties are resolved by whichever row Oracle places first unless you add a tie-breaker to the ORDER BY, such as employee_id. If all rows tied for the maximum should be returned, use a ranking approach that preserves ties, or join the table to a grouped query that calculates the maximum for each department. The ranking’s order must use the same intended collation as the maximum calculation if collation-sensitive text is involved.
Analytic MAX and useful distinctions
Oracle can also use MAX as an analytic function. Unlike a regular aggregate, an analytic maximum can return a result on each input row while comparing values across a window or partition. For example, MAX(last_name) OVER (PARTITION BY department_id) shows the greatest last name in each department beside every employee row in that department. It does not reduce the result to one row per department.
Do not confuse this with sorting a query. ORDER BY last_name DESC sorts rows; it does not itself calculate a single aggregate value. Similarly, a maximum string is not necessarily the “latest” string in time. If you need the row with the newest timestamp, use the timestamp column to identify it. If you need the longest text, compare lengths, rather than assuming MAX(text_column) does that.
Common mistakes and checks
When a result seems wrong, first decide what “maximum” should mean for the data. If the goal is numeric, confirm that the values are numeric rather than character strings. If it is a human-language alphabetic maximum, confirm the collation. If it is newest, longest, or tied to a particular row, use the corresponding date, length, or row-selection logic.
- Check whether the expression is character data or a number converted to text.
- Check the column’s collation and any relevant database or session sorting settings.
- Look for NULLs, empty strings, leading or trailing spaces, and inconsistent capitalization.
- Use
GROUP BYonly when you want a separate result for each group. - Use a ranking or join pattern when you need other columns from the row containing the maximum.
Oracle does not support every data type as an argument to every aggregate. In particular, large-object types such as CLOB are not ordinary character values that can simply be passed to MAX for full-text ordering. If the source is a LOB, identify an appropriate supported expression or a different comparison strategy; do not assume a query that works on VARCHAR2 will work unchanged on a LOB.
Frequently Asked Questions
Does Oracle MAX work on VARCHAR2?
Yes. MAX can return the highest non-NULL VARCHAR2 value according to the applicable collation. It compares the text as character data; it does not measure string length or infer numeric meaning.
Does MAX return the last value alphabetically?
It returns the value highest in the applicable sort order. That often resembles the alphabetically last value for simple, consistently capitalized text, but collation, case, accents, and other characters can affect the result.
Why does MAX return 9 instead of 10?
If the column contains character strings, Oracle compares them as text. A string beginning with 9 can sort above one beginning with 1. Store quantities as numbers or convert validated text to a numeric type when numeric comparison is intended.
Does MAX ignore NULL values in Oracle?
Yes. NULLs are ignored when other values exist. If all values in the aggregate are NULL, or there are no rows to aggregate, the result is NULL.
Can I use MAX to return the whole row with the largest string?
Not by itself. MAX returns the maximum expression value. Use an analytic ranking, a subquery, or a join to identify the row, and decide explicitly how ties should be handled.
Related
- Balance C/D in Accounting: Meaning and Examples
- A/B Split Testing: Meaning, Method, and Common Mistakes
- Balance of Payments Theory: Meaning and How It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Checkbook Register: Setup GuideThe core formula for a checkbook register in Excel is a running balance formula: =SUM($D$2:D2) entered in the…
- Trim Number in Excel Formula: Remove Decimal PlacesTo remove decimal places without rounding, use TRUNC. With the original number in A2 and a target of…
- Count Unique Values in Excel With One or More CriteriaIn Microsoft 365 for Windows, count distinct nonblank values that meet one condition with =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,(B2:B100=E2)*(A2:A100<>"")))),0). This counts the…
- Excel Interpolate Function: How to Calculate ValuesMicrosoft Excel does not have a built-in function named INTERPOLATE. To estimate a value between two known points,…
- Databricks User-Defined Functions: How They Work in PracticeDatabricks user-defined functions (UDFs) let you apply custom logic to data in SQL or Spark when built-in functions…
- Excel Me Divide Ka Formula? (Aur Kaise Use Karein)Excel me divide karne ka formula hai =A1/B1 (forward slash). Jaise aap calculator me ‘/’ use karte hain,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.