Use output of SHOW PARTITION commands in Sub-Query/CTE/Function

[pshah83]

I am using SHOW PARTITIONS <<table_name>> to get all the partitions of a table. I want to use max() on the output of this command to get the latest partition for the table.

However, I am not able to use SHOW PARTITIONS <<table_name>> in a CTE/sub-query/table function. All of them fail with syntax error. Any inputs

with t as (
SHOW PARTITIONS <<table_name>>
)
select * from t

OR

select * from (
SHOW PARTITIONS <<table_name>>
)