Find the top 5 MAX() values from an SQL table and then performi an AVG() on that table without them

I want to be able to perform an avg() on a column after removing the 5 highest values in it and see that the stddev is not above a certain number. This has to be done entirely as a PL/SQL query.

EDIT: To clarify, I have a data set that contains values in a certain range and tracks latency. I want to know whether the AVG() of those values is due to a general rise in latency, or due to a few values with a very high stddev. I.e - (1, 2, 1, 3, 12311) as op开发者_运维知识库posed to (122, 124, 111, 212). I also need to achieve this via an SQL query due to our monitoring software's limitations.

You can use row_number to find the top 5 values, and filter them out in a where clause:

select  avg(col1)
from    (
        select  row_number() over (order by col1 desc) as rn
        ,       *
        from    YourTable
        ) as SubQueryAlias
where   rn > 5

select column_name1 from 
(
  select column_name1 from table_name order by nvl(column_name,0) desc
)a 
where rownum<6 

(the nvl is done to omit the null value if there is/are any in the column column_name)

Well, the most efficient way to do it would be to calculate (sum(all values) - sum(top 5 values)) / (row_count - 5)

SELECT SUM(val) AS top5sum FROM table ORDER BY val DESC LIMIT 5

SELECT SUM(val) AS allsum FROM table

SELECT (COUNT(*) - 5) AS bottomCount FROM table

The average is then (allsum - top5sum) / bottomCount

First, get the MAX 5 values:

SELECT TOP 5 RowId FROM Table ORDER BY Column

Now use this in your main statement:

SELECT AVG(Column) FROM Table WHERE RowId NOT IN (SELECT TOP 5 RowId FROM Table ORDER BY Column)