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

Home > Articles

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

This chapter is from the book

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.1Figure 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 isn’t 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.

  • + Share This
  • 🔖 Save To Your Account

Discussions

comments powered by Disqus