sql - How to get the second last partition in hive (HQL) -

i have hive table below partitions:

part_date='07-12-2016'

part_date='04-12-2016'

part_date='01-12-2016'

now how can first partition less recent partition in hive query. assuming partition values date when data ingested table. in case should part_date='04-12-2016'.

so query should like:

select part_date table_a part_date > second_last_partition(ie 04-12-2016);

thanks in advance

you can use row_number window function , second latest row.

select part_date (select part_date,row_number() over(order part_date desc) rn        table_a) t rn = 2 

i assume part_date column distinct. if not, should use dense_rank function.