SELECT percentile_cont(0.5) OVER (PARTITION BY room ORDER BY data) AS median, room FROM [io_sensor_data.moscone_io13] WHERE sensortype='temperature'

SELECT MAX(median) AS median, room FROM ( SELECT percentile_cont(0.5) OVER (PARTITION BY room ORDER BY data) AS median, room FROM [io_sensor_data.moscone_io13] WHERE sensortype='temperature' ) GROUP BY room

SELECT DENSE_RANK() OVER (ORDER BY median) rank, median, room FROM ( SELECT MAX(median) AS median, room FROM ( SELECT percentile_cont(0.5) OVER (PARTITION BY room ORDER BY data) AS median, room FROM [io_sensor_data.moscone_io13] WHERE sensortype='temperature' ) GROUP BY room )