MAX_PT() function

Prerequisites

Before you begin, ensure that you have:

• An AnalyticDB for MySQL cluster running V3.2.4.0 or later

  Note

  To view or update the minor version of a cluster, go to the Configuration Information section on the Cluster Information page in the AnalyticDB for MySQL console.

• A MaxCompute project created in the same region as the AnalyticDB for MySQL cluster

Usage notes

• MAX_PT() works only with MaxCompute external tables. Calling it on internal tables or other types of external tables returns an error. To create a MaxCompute external table, see CREATE EXTERNAL TABLE.

• The target table must be a partitioned table with data in at least one partition. If either condition is not met, MAX_PT() fails.

Syntax

MAX_PT(<table_full_name>)

Parameter

Return value

Returns the alphabetically largest value of the level-1 partition key column. The return value type matches the partition key column type defined in the MaxCompute external table. Supported types: BOOLEAN, BIGINT, and STRING.

Example

This example creates a MaxCompute external table named customer with partitions 20241209, 20241210, and 20241211, where dt is the partition key column.

CREATE EXTERNAL TABLE IF NOT EXISTS customer (
    id int,
    name varchar(1023),
    age int,
    dt STRING
) ENGINE='ODPS'
TABLE_PROPERTIES='{
    "accessid":"LTAILd4****",
    "endpoint":"http://service.cn-hangzhou.maxcompute.aliyun.com/api",
    "accesskey":"4A5Q7ZVzcYnWMQPysX****",
    "partition_column":"dt",
    "project_name":"test_adb",
    "table_name":"person"
}';

To count rows in the most recent partition, filter on MAX_PT() in the WHERE clause. AnalyticDB for MySQL reads only the 20241211 partition instead of scanning all partitions:

SELECT COUNT(*) FROM customer WHERE dt = MAX_PT('customer');