MAX() and MIN() Functions SQL

There are two SQL functions to help get the largest or smallest value from a column, MAX() and MIN() respectively. These work pretty much as expected. The syntax for MAX():

SELECT MAX(column) FROM table

This will get you the MAX number. However, we want more information that is associated with the row that contains this maximum price. Specifically from our monopoly_prop table we want to know the highest priced property and to which color group it belongs.

prop_idnamepricegroup
1Baltic Ave60purple
2Mediterranean Ave60purple
3Reading Railroad200rr
4Oriental Ave100light blue
5Vermont Ave100light blue
6Connecticut Ave120light blue

This can be found using the following:

SELECT * FROM monopoly_prop WHERE price = (SELECT MAX(price) FROM monopoly_prop)

We will find out that the highest priced item in our table is Reading Railroad. Because MAX is an aggregate function, we cannot use it straight up as a comparison in the WHERE clause against the price column. Without SELECTing the max price in the WHERE clause SQL would actually return all of the rows in the table. When used with character strings MAX() will return the one that begins with the letter last in alphabetic order. For example if we queried MAX(name) in our table above we would get "Vermont Ave" as our result.

The MIN() function is the complement to MAX(). It will find the lowest amount in a numeric string. In a character string it will return the one earliest in alphabetical order. Using MIN in our query above:

SELECT * FROM monopoly_prop WHERE price = (SELECT MIN(price) FROM monopoly_prop)

Would return two results, Baltic and Mediterranean Avenues.

< MATCH AGAINST Function | NOW Function SQL >


Send notes in disappearing ink!

Interesting Pages