In Oracle, find a number that exceeds 80% of a set of numbers

Question

Suppose I have a table with a column of integers in Oracle. There are a large number of lines; somewhere in the millions. I want to write a query that returns me an integer that exceeds 80% of all numbers in the table. What is the best way to approach this?

If that matters, it's Oracle 10g r1.

Answer 1

It looks like you want to use the PERCENTILE_DISC function if you want to get the actual value from the set, or PERCENTILE_CONT if you want to interpolate the value for a specific percentile, say 80%:

SELECT PERCENTILE_DISC(0.8) WITHIN GROUP(ORDER BY integer_col ASC) FROM some_table 

EDIT

If you use PERCENTILE_DISC, it will return the actual value from the dataset, so if you need a larger value, you want to increase it by 1 (for an integer column).

Answer 2

I think you could use the NTILE function to split the input into 5 buckets, then select MIN (Column) from the top bucket.