How to get second largest value as second value in esper

Question

I'm trying to get the second value as the second largest value in Esper. But, unfortunately, it says the nested subquery is not working. So, how can I get the second largest value in Esper?

My Query is as follows:

SELECT max(averageTemperature) as Temp1, 
  (
    select max(jfk_alias.averageTemperature) 
    from jfk as jfk_alias 
    where jfk_alias.averageTemperature not in (
      select max(averageTemperature) 
      from jfk) 
) as Temp2
FROM JFK

Answer 1

You can use windows functions to order the averageTemperature by descending order, then select the 2nd row.

SELECT *
FROM (
    SELECT  averageTemperature,
            dense_rank() over(order by averageTemperature desc) as rn_max
    FROM jfk
) a
WHERE rn_max = 2

EDIT : dense_rank instead of row_number per Andrew's suggestion

Answer 2

The "sorted" aggregation function is what you want to use. It has various access methods.