I have a table of containing records with negative numbers:

(my actual records and values are more numerous and more complex, but this should help illustrate the issue)

I want to get the minimum, first quartile, median, third quartile, maximum of the temperature values, but in reverse.

For instance, in my example I would have:

The problem as I see it is that my numbers are negative. So when I run:

SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY "city_temperatures"."temperature") AS percentile_temperature FROM "city_temperatures"

I actually get the value third quartile as opposed to the first quartile.

What's the best way to handle negative numbers in a query like this?

CodePudding user response：

Add DESC to ORDER BY?

SELECT percentile_cont(0.25) WITHIN GROUP (ORDER BY t.temperature DESC) AS pct_temp
FROM city_temperatures t;

You might get all of it as array in a single calls with:

SELECT percentile_cont('{0,0.25,0.5,0.75,1}'::float8[])
       WITHIN GROUP (ORDER BY t.temperature DESC) AS pct_temps
FROM city_temperatures t;