MAX Function in SQL - sql - sql tutorial - learn sql
- The SQL MAX function is used to return the maximum value of an expression in a SELECT statement.
- In other words, SQL MAX function is used to find out the record with maximum value among a record set.
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialSyntax
Syntax
- The syntax for the MAX function is,
SELECT MAX (<expression>)
FROM "table_name";
can be a column name or an arithmetic operation. An arithmetic operation can include more than one column, such as ("column1" + "column2"). - It is also possible to have one or more columns in addition to the MAX function in the SELECT statement. In those cases, these columns need to be part of the GROUP BY clause as well:
SELECT "column_name1", "column_name2", ... "column_nameN", MAX (<expression>)
FROM "table_name";
GROUP BY "column_name1", "column_name2", ... "column_nameN";
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialExamples
Examples
- We use the following table for our examples.
Table Store_Information
Store_Name | Sales | Txn_Date |
---|---|---|
Los Angeles | 1500 | Jan-05-1999 |
San Diego | 250 | Jan-07-1999 |
Los Angeles | 300 | Jan-08-1999 |
Boston | 700 | Jan-08-1999 |
Example 1: MAX function on a column
- To find the maximum sales amount, we type in,
SELECT MAX(Sales) FROM Store_Information;
Result:
MAX(Sales) |
---|
1500 |
- 1500 represents the maximum value of all Sales entries: 1500, 250, 300, and 700.
Example 2: MAX function on an arithmetic operation
- Assume that sales tax is 10% of the sales amount, we use the following SQL statement to get the maximum sales tax amount:
SELECT MAX(Sales*0.1) FROM Store_Information;
Result:
MAX(Sales*0.1) |
---|
150 |
- SQL will first calculate "Sales*0.1" and then apply the MAX function to the result for the final answer.
Example 3: MAX function with a GROUP BY clause
- To get the maximum sales amount for each store, we type in,
SELECT Store_Name, MAX(Sales) FROM Store_Information GROUP BY Store_Name;
Result:
Store_Name | MAX(Sales) |
---|---|
Los Angeles | 1500 |
San Diego | 250 |
Boston | 700 |
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialSQL Exercise Aggregate Function Having MAX
SQL Exercise Aggregate Function Having MAX
