Dofactory.com
Dofactory.com

SQL MAX Function

MAX returns the maximum value from a set of values.

This function ignores NULL values.

Example

#

This example returns the maximum, or highest, product price.

SELECT MAX(UnitPrice) AS Maximum
  FROM Product
Result:  1 record
Maximum
263.50

Syntax

Syntax of the MAX function.

MAX(value)

value -- a number, column, or subquery.

More Examples

MAX with GROUP BY

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
Problem: List the largest orders by month for the year 2013.
SELECT MONTH(OrderDate) AS 'Month',
       MAX(TotalAmount) AS 'Max Order Amount'
  FROM [Order]
 WHERE YEAR(OrderDate) = 2013
 GROUP BY MONTH(OrderDate)
Result:  12 records
Month Max Order Amount
1 11493.20
2 5793.10
3 10495.60
4 10588.50
5 10191.70

MAX with GROUP BY

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
Problem: List the most expensive product for each supplier.
SELECT S.CompanyName, 
       MAX(UnitPrice) AS 'Highest Price'
  FROM Product P
  JOIN Supplier S ON S.Id = P.SupplierId
 GROUP BY S.CompanyName
Result:  29 records
CompanyName Highest Price
Aux joyeux ecclésiastiques 263.50
Bigfoot Breweries 18.00
Cooperativa de Quesos 'Las Cabras' 38.00
Escargots Nouveaux 13.25
Exotic Liquids 19.00

You may also like



Last updated on Dec 21, 2023