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



Guides


vsn 3.1