MaxCompute:MAX_PT

Usage notes

• You can also use a standard SQL statement instead of the statement in which the MAX_PT function is used. For example, you can use SELECT * FROM table WHERE pt=MAX_PT("table"); instead of SELECT * FROM table WHERE pt = (SELECT MAX(pt) FROM table);.

  Note

  MaxCompute does not provide the MIN_PT function. If you need to obtain the partition that contains the fewest amount of data in a partitioned table, you cannot use the SQL statement SELECT * FROM table WHERE pt=MIN_PT("table");. Instead, you can use the standard SQL statement SELECT * FROM table WHERE pt= (SELECT MIN(pt) FROM table); to achieve a similar effect as the MAX_PT function.

• If all partitions in the table are empty, the MAX_PT function fails to be executed. You must make sure that at least one partition is not empty.

• OSS external tables also support the MAX_PT function, with behavior consistent with internal tables.

Syntax

MAX_PT(<table_full_name>)

Parameters

table_full_name: required. A value of the STRING type. This parameter specifies the name of the table. You must have read permissions on the table.

Return value

The name of the largest level-1 partition is returned.

Examples

• Example 1: The tbl table is a partitioned table. The partitions in the table are 20120901 and 20120902, both of which contain data. If you execute the following statement, the MAX_PT function returns '20120902', and the MaxCompute SQL statement reads data from the 20120902 partition. Sample statements:

  SELECT * FROM tbl WHERE pt= MAX_PT('tbl');
  -- The preceding statement is equivalent to the following statement: 
  SELECT * FROM tbl WHERE pt= (SELECT MAX(pt) FROM tbl);

• Example 2: If a partitioned table contains multiple levels of partitions, use the standard SQL statement to obtain data from the largest partition. Sample statement:

  SELECT * FROM table WHERE pt1 = (SELECT MAX(pt1) FROM table) AND pt2 = (SELECT MAX(pt2) FROM table WHERE pt1= (SELECT MAX(pt1) FROM table));

Related functions

For more information, see Other functions.