What Is An SQL Max Query?

Websites and computing applications often store data in databases. Database systems such as MySQL and Oracle are common for both Web and desktop programs. When these programs need to retrieve data from a database, they execute query statements on it. Queries can retrieve specific data items, by referring to table and column names. The MAX function in SQL normally appears inside the select statement, which is a data query. The MAX function is particularly useful for numerical data, as it retrieves the maximum value in a column.

In this article we'll briefly go over the purpose and use of the max clause in select query statements, referring in particular to the MySQL database system.

Data

Databases for Web and desktop applications use various systems, but most of them use SQL statements. Developers build application user interfaces on top of databases, writing SQL statements to insert, update and query data. When you visit a website built using a database, the server side scripts within the site will typically execute standard query statements on the data for common tasks.

Databases store many different types of data value, including text strings and numbers. A typical database includes one or more table, with each table having a few columns. Each record in a table may store a value in each column. For example, a table to store product details could have a column representing price. Each record in the table would represent a single product, and would have a value in the price column.

When applications built on databases need to retrieve data, they use query statements, as in the following SQL example:

SELECT price FROM product;

This would retrieve the values in the price column for every record in the product table.

Max Queries

Developers can include additional conditions within query statements, tailoring the data values retrieved. For example, if an application needs to establish the product with the highest cost, it can use a MAX query.

The following code demonstrates:

SELECT MAX(price) FROM product;

This query should result in a single data item, representing the maximum value in the price column. The code uses the MAX clause as part of the statement, including the column name as a parameter to the function. The rest of the statement is the same as a standard query.

As Clause

The MAX clause also appears along with the AS clause. The following code demonstrates:

SELECT MAX(price) AS top_price FROM product;

The AS clause allows developers to assign a name to use as a reference for the resulting data value. This can be particularly useful in cases where the query is selecting a special data item such as the maximum value in a column. Depending on the database system in use, the name specified in the AS clause may appear in the application user interface.

Where Clause

SQL queries involving MAX often also involve WHERE clauses. The following statement demonstrates:

SELECT MAX(price) FROM order WHERE product_type=3;

This would return the maximum price record for a particular product type. The product type could be an additional column in the product table. Scripts for Web and desktop applications in languages such as PHP, ASP and Java can use variables to dynamically load data values into queries, such as the product type in this case.

Grouping

SQL statements sometimes use the MAX clause in statements involving grouping. The following code demonstrates a grouping query:

SELECT product_type, MAX(price) FROM product GROUP BY product_type;

This would be useful if the developer needs to retrieve the maximum price for each product type. If the query only specifies the column it needs the maximum for, it will only return a single result. Since this statement specifies two columns, one of which is enclosed in a MAX clause, it must also specify which column to group the results by, so that the maximum for each type is returned.

Links:

MySQL :: MySQL 5.0 Reference Manual :: 12.2.8 SELECT Syntax
MySQL :: MySQL 5.0 Reference Manual :: 11.15.1 GROUP BY (Aggregate) Functions