Finding a Minimum with MIN()
Use the aggregate function MIN() to find the minimum of a set of values.
To find the minimum of a set of values:
Type:
MIN(expr)
expr is a column name, literal, or expression. The result has the same data type as expr.
Listing 6.1 and Figure 6.1 show some queries that involve MIN(). The first query returns the price of the lowest-priced book. The second query returns the earliest publication date. The third query returns the number of pages in the shortest history book.
Listing 6.1 Some MIN() queries. See Figure 6.1 for the results.
SELECT MIN(price) AS "Min price" FROM titles; SELECT MIN(pubdate) AS "Earliest pubdate" FROM titles; SELECT MIN(pages) AS "Min history pages" FROM titles WHERE type = 'history';
Figure 6.1 Results of Listing 6.1.
TIPS
MIN() works with character, numeric, and datetime data types.
With character data columns, MIN() finds the value that is lowest in the sort sequence; see Sorting Rows with ORDER BY in Chapter 4.
DISTINCT isnt meaningful with MIN(); 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 MIN() returns.