In Oracle, find number which is larger than 80% of a set of a numbers

Question

Assume I have a table with a column of integers in Oracle. There are a good amount of rows; somewhere in the millions. I want to write a query that gives me back an integer that is larger than 80% of all of the numbers in table. What is the best way to approach this?

If it matters, this is Oracle 10g r1.

Answer 1

Sounds like you want to use the PERCENTILE_DISC function if you want an actual value from the set, or PERCENTILE_CONT if you want an interpolated value for a particular 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 an actual value from the dataset, so if you wanted a larger value, you'd want to increment that by 1 (for an integer column).