I'm using MySQL 5.6.16

SELECT TABLE_NAME, partition_name, partition_ordinal_position FROM information_schema.PARTITIONS
WHERE table_name IN(
    SELECT DISTINCT table_name FROM information_schema.PARTITIONS WHERE table_schema = 'cbb' AND partition_name IS NOT NULL
)
ORDER BY partition_ordinal_position asc
;

I want to get the partition_name from the first and the last partition_ordinal_position grouped by table_name.

Like this :

CodePudding user response：

Test this:

SELECT DISTINCT
       table_name, 
       FIRST_VALUE(partition_name) OVER (PARTITION BY table_name ORDER BY partition_ordinal_position ASC) first_partition, 
       FIRST_VALUE(partition_name) OVER (PARTITION BY table_name ORDER BY partition_ordinal_position DESC) last_partition
FROM information_schema.partitions
WHERE table_schema = 'cbb' 
  AND partition_name IS NOT NULL;

I'm using MySQL 5.6.16 – Lunartist

SELECT DISTINCT
       t0.table_name, 
       ( SELECT t1.partition_name
         FROM information_schema.partitions t1
         WHERE t0.table_schema = t1.table_schema
           AND t0.table_name = t1.table_name
         ORDER BY t1.partition_ordinal_position ASC 
         LIMIT 1) first_partition, 
       ( SELECT t2.partition_name
         FROM information_schema.partitions t2
         WHERE t0.table_schema = t2.table_schema
           AND t0.table_name = t2.table_name
         ORDER BY t2.partition_ordinal_position DESC 
         LIMIT 1) last_partition
FROM information_schema.partitions t0
WHERE t0.table_schema = 'cbb' 
  AND t0.partition_name IS NOT NULL;