I am trying to retrieve the max, min and the 90th percentile from a table of results.
I want the 90th percentile for duration based on the timestamp_ in asc order.
My Table looks like this:
I have 2 queries to retrive this info, but is there a simpler solution by using one query. here are my queries:
Select MIN(DURATION), max(DURATION)
select DURATION as nine from t
where TIMESTAMP_ =
Percentile_disc(0.90) within group (order by TIMESTAMP_) AS nth
I got the answer from the guys over at stackoverflow. This was the query that i was looking for :
SELECT duration, max_duration, min_duration
FROM (SELECT duration, ts,
Percentile_disc(0.90) within GROUP(ORDER BY ts) OVER() nth,
MAX(duration) OVER() max_duration,
MIN(duration) OVER() min_duration
WHERE ts = nth;