Returns the name of the largest level-1 partition that contains data in a partitioned table, and reads the data of that partition. Partitions are ranked in alphabetical order.
Usage notes
MAX_PTonly evaluates level-1 partitions. For tables with multiple partition levels, use a standard SQL subquery instead.If all partitions in the table are empty,
MAX_PTreturns an error. Make sure at least one partition contains data before calling this function.OSS external tables support
MAX_PTwith the same behavior as internal tables.MaxCompute does not provide a
MIN_PTfunction. To get the smallest partition, use:SELECT * FROM table WHERE pt = (SELECT MIN(pt) FROM table);
Syntax
MAX_PT(<table_full_name>)Parameters
| Parameter | Required | Type | Description |
|---|---|---|---|
table_full_name | Yes | STRING | The full name of the table. You must have read permissions on the table. |
Return value
The name of the largest level-1 partition that contains data.
Partitions created with ALTER TABLE that contain no data are excluded from the result.
Examples
Query the latest partition
The tbl table has two partitions — 20120901 and 20120902 — both containing data.
SELECT * FROM tbl WHERE pt = MAX_PT('tbl');This returns data from the 20120902 partition. The statement above is equivalent to:
SELECT * FROM tbl WHERE pt = (SELECT MAX(pt) FROM tbl);Query multi-level partitions
MAX_PT only works with level-1 partitions. For tables with multiple partition levels, use standard SQL subqueries:
SELECT * FROM table
WHERE pt1 = (SELECT MAX(pt1) FROM table)
AND pt2 = (SELECT MAX(pt2) FROM table WHERE pt1 = (SELECT MAX(pt1) FROM table));