Publishers of technology books, eBooks, and videos for creative people

Home > Articles

  • Print
  • + Share This
This chapter is from the book

This chapter is from the book

Finding a Maximum with MAX()

Use the aggregate function MAX() to find the maximum of a set of values.

To find the maximum of a set of values:

  • Type:

    MAX(expr)

    expr is a column name, literal, or expression. The result has the same data type as expr.

Listing 6.2 and Figure 6.2 show some queries that involve MAX(). The first query returns the author’s last name that is last alphabetically. The second query returns the prices of the cheapest and most expensive books, and the price range. The third query returns the highest revenue (= price ´ sales) among the history books.

Listing 6.2 Some MAX() queries. See Figure 6.2 for the results.

SELECT MAX(au_lname) AS "Max last name"
  FROM authors;


SELECT
    MIN(price) AS "Min price",
    MAX(price) AS "Max price",
    MAX(price) - MIN(price) AS "Range"
  FROM titles;


SELECT MAX(price * sales)
         AS "Max history revenue"
  FROM titles
  WHERE type = 'history';

Figure 6.2Figure 6.2 Results of Listing 6.2.


TIPS

  • MAX() works with character, numeric, and datetime data types.

  • With character data columns, MAX() finds the value that is highest in the sort sequence; see “Sorting Rows with ORDER BY” in Chapter 4.

  • DISTINCT isn’t meaningful with MAX(); see “Aggregating Distinct Values with DISTINCT” later in this chapter.

  • String comparisons may be case-insensitive or case-sensitive, depending on your DBMS; see the DBMS Tip in “Filtering Rows with WHERE” in Chapter 4.

    When comparing two VARCHAR strings for equality, your DBMS may right-pad the shorter string with spaces and compare the strings position-by-position. In this case, the strings ‘Jack’ and ‘Jack ‘ are equal. Refer to your DBMS documentation (or experiment) to determine which string MAX() returns.

  • + Share This
  • 🔖 Save To Your Account