MAX (window)

Function MAX is also found in the following categories: Aggregate functions.

Syntax

MAX( value
     TOTAL | WITHIN ... | AMONG ...
   )

More info:

MAX( value
     TOTAL | WITHIN ... | AMONG ...
     [ BEFORE FILTER BY ... ]
   )

More info:

Description

Returns the maximum value.

If value:

  • number — Returns the largest number.
  • date — Returns the latest date.
  • string — Returns the last value in the alphabetic order.

Argument types:

  • valueBoolean | Date | Datetime | Fractional number | Integer | String | UUID

Return type: Same type as (value)

Example

Source data

Date City Category Orders Profit
'2019-03-01' 'London' 'Office Supplies' 8 120.80
'2019-03-04' 'London' 'Office Supplies' 2 100.00
'2019-03-05' 'London' 'Furniture' 1 750.00
'2019-03-02' 'Moscow' 'Furniture' 2 1250.50
'2019-03-03' 'Moscow' 'Office Supplies' 4 85.00
'2019-03-01' 'San Francisco' 'Office Supplies' 23 723.00
'2019-03-01' 'San Francisco' 'Furniture' 1 1000.00
'2019-03-03' 'San Francisco' 'Furniture' 4 4000.00
'2019-03-02' 'Detroit' 'Furniture' 5 3700.00
'2019-03-04' 'Detroit' 'Office Supplies' 25 1200.00
'2019-03-04' 'Detroit' 'Furniture' 2 3500.00

Grouped by [City], [Category].

Sorted by [City], [Category].

Result

[City] [Category] SUM([Orders]) MAX(SUM([Orders]) TOTAL) MAX(SUM([Orders]) WITHIN [City]) MAX(SUM([Orders]) AMONG [City])
'Detroit' 'Furniture' 7 25 25 7
'Detroit' 'Office Supplies' 25 25 25 25
'London' 'Furniture' 1 25 10 7
'London' 'Office Supplies' 10 25 10 25
'Moscow' 'Furniture' 2 25 4 7
'Moscow' 'Office Supplies' 4 25 4 25
'San Francisco' 'Furniture' 5 25 23 7
'San Francisco' 'Office Supplies' 23 25 23 25

Data source support

ClickHouse 19.13, Microsoft SQL Server 2017 (14.0), MySQL 5.6, PostgreSQL 9.3.

Previous
Next